﻿using Aspose.Cells;
using Dapper;
using DevExpress.XtraEditors;
using DevExpress.XtraGrid;
using DevExpress.XtraGrid.Views.Grid;
using DevExpress.XtraSplashScreen;
using DevExpress.XtraWaitForm;
using FileConver.DAL;
using InvoiceImport.Models;
using SHelper;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Windows.Forms;

namespace InvoiceImport
{
    public partial class MainFrm : DevExpress.XtraEditors.XtraForm
    {
        public MainFrm()
        {
            InitializeComponent();
        }
        SaleBillDataDataContext sb;
        string con = ConfigurationManager.ConnectionStrings["InvoiceImport.Properties.Settings.UFDATA_102_2013ConnectionString"].ConnectionString;
        private IDbConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["InvoiceImport.Properties.Settings.UFDATA_102_2013ConnectionString"].ConnectionString);
        string code = "102";

        private void MainFrm_Load(object sender, EventArgs e)
        {
            #region 显示账套
            try
            {
                //得到当前连接字符串
                string str = ConfigurationManager.ConnectionStrings["InvoiceImport.Properties.Settings.UFDATA_102_2013ConnectionString"].ConnectionString;
                string nameStr = str.Substring(str.IndexOf("UFDATA_"), 15);

                //SQL语句 
                string sql = "SELECT * FROM Master..SysDatabases where name like 'UFDATA_%' ORDER BY Name desc";//用于查询账套名的SQL语句

                var query = DataHelper.ExecuteDataSet(sql, null).Tables[0];
                this.ServerBox.DataSource = query;
                this.ServerBox.SelectedText = "dbid";
                this.ServerBox.DisplayMember = "name";
                this.ServerBox.Text = nameStr;//设置默认选择项
            }
            catch (Exception ex)
            {
                XtraMessageBox.Show("账套加载失败,原因:" + ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
            #endregion
        }
        //收入导入
        private void ImportBtn_Click(object sender, EventArgs e)
        {
            string str3 = ConfigurationManager.ConnectionStrings["InvoiceImport.Properties.Settings.UFDATA_102_2013ConnectionString"].ConnectionString;
            string str1 = str3.Substring(0, str3.IndexOf(" Catalog=") + 9);
            string str2 = str3.Substring(str3.IndexOf(" Catalog=") + 24);
            str3 = str1 + ServerBox.Text + str2;
            //获取新的连接字符串
            IDbConnection connNew = new SqlConnection(str3);

            sb = new SaleBillDataDataContext(str3);
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "(*.xls,*.xlsx;)|*.xls;*.xlsx;";
            if (ofd.ShowDialog() == DialogResult.Cancel) return;
            var wf = new SplashScreenManager(this, typeof(DemoWaitForm), true, true);//进程
            wf.ShowWaitForm();
            wf.SetWaitFormCaption("正在导入...");
            string str = "";
            Workbook wb = new Workbook(ofd.FileName);
            Worksheet ws = wb.Worksheets[0];
            int count = 0, count1 = 1; int maxid = sb.SaleBillVouches.Max(p => p.SBVID); int maxautoid = sb.SaleBillVouch1s.Max(p => p.AutoID);
            int maxid1 = sb.DispatchLists.Max(p => p.DLID); string maxid2 = sb.DispatchLists.Max(p => p.cDLCode); int maxautoid1 = sb.DispatchList1s.Max(p => p.AutoID);
            int iDLsID = sb.DispatchList1s.Max(p => p.iDLsID);
            //var paycode = sb.PayConditions.SingleOrDefault(p => p.cPayName == "n/" + (DateTime.DaysInMonth(DateTime.Today.Year, DateTime.Today.AddMonths(1).Month)));
            #region 专用发票导入
            if (GetCellStringValue(ws.Cells[1, 0].Value) == "购货单位名称")
            {
                int NowCount = 0;
                for (int v = 1; v < ws.Cells.MaxDataRow; v += 26)
                {
                    NowCount++;
                    wf.SetWaitFormCaption(string.Format("正在导入第 {0} 张", NowCount));
                    #region 检查基础信息
                    count++;
                    Inventory inventory; Person person; Customer customer;
                    string temp = GetCellStringValue(ws.Cells[15 + v, 1].StringValue);
                    try
                    {
                        inventory = sb.Inventories.Single(p => p.cInvAddCode == GetInventoryCode(temp));//判断存货代码
                    }
                    catch
                    {
                        str += string.Format("第 {0} 张发票D/N号(存货代码)在用友系统中不存在或有重复,请维护好后再导入!\n", count);
                        continue;
                    }
                    try
                    {
                        person = sb.Persons.Single(p => p.cPersonName == GetCellStringValue(ws.Cells[16 + v, 1].Value));
                    }
                    catch
                    {
                        str += string.Format("第 {0} 张发票业务员信息在用友系统不一致,请维护好后再导入!\n", count);
                        continue;
                    }
                    try
                    {
                        var _cCusName = GetCellStringValue(ws.Cells[0 + v, 1].Value);
                        customer = connNew.Query<Customer>($"select * from Customer where cCusName= '{_cCusName}'").FirstOrDefault();
                        //customer = sb.Customers.Single(p => p.cCusName == GetCellStringValue(ws.Cells[0 + v, 1].Value));
                    }
                    catch
                    {
                        str += string.Format("第 {0} 张发票客户信息在用友系统中不存在,请维护好后再导入!\n", count);
                        continue;
                    }
                    #endregion
                    #region 生成主表
                    SaleBillVouch sbv = new SaleBillVouch(); DispatchList dis = new DispatchList();
                    if (!string.IsNullOrEmpty(GetCellStringValue(ws.Cells[18 + v, 1].Value)))
                    {
                        dis.cSBVCode = sbv.cSBVCode = GetCellStringValue(ws.Cells[15 + v, 1].Value) + " #" + GetCellStringValue(ws.Cells[18 + v, 1].Value);//发票号
                    }
                    else
                    {
                        dis.cSBVCode = sbv.cSBVCode = GetCellStringValue(ws.Cells[15 + v, 1].Value);//发票号
                    }
                    if (sb.SaleBillVouches.Any(p => p.cSBVCode == sbv.cSBVCode))
                    {
                        str += string.Format("第 {0} 张发票在用友系统内已存在,导入失败!\n", count);
                        continue;
                    }
                    dis.SBVID = sbv.SBVID = maxid + count;//发票主表标识
                    dis.DLID = maxid1 + count;
                    dis.cDLCode = sbv.cDLCode = (int.Parse(maxid2) + count).ToString("0000000000");
                    sbv.cVouchType = "27"; //发票类型
                    dis.cVouchType = "05";
                    sbv.bIAFirst = false;
                    dis.cSTCode = sbv.cSTCode = "1";//销售类型编码
                    dis.dDate = sbv.dDate = DateTime.Today;//单据日期
                    dis.cDepCode = sbv.cDepCode = GetDepartCode(customer.cCCCode);//区域（501or601）
                    dis.cPersonCode = sbv.cPersonCode = person.cPersonCode;//人员编码
                    dis.cCusCode = sbv.cCusCode = customer.cCusCode;//客户编码
                    dis.cCusName = customer.cCusName;
                    dis.cPayCode = sbv.cPayCode = customer.cCusPayCond;//付款条件
                    string sui = GetCellStringValue(ws.Cells[5 + v, 6].Value).Replace('%', '\0');
                    dis.iTaxRate = sbv.iTaxRate = double.Parse(sui == "" ? "0" : sui) * 100;//税率
                    dis.cBusType = sbv.cBusType = "普通销售";//销售类型
                    sbv.cCusName = customer.cCusName;//客户名称
                    sbv.cSource = "销售";//单据来源
                    sbv.iVTid = 17;//单据模板号
                    dis.cMaker = dis.cVerifier = sbv.cChecker = sbv.cMaker = "蔡蕾";
                    dis.iSale = 1;
                    dis.iVTid = 71;
                    sbv.bIAFirst = dis.bIAFirst = false;
                    dis.csvouchtype = "27";
                    dis.bneedbill = true;
                    if (string.IsNullOrEmpty(GetCellStringValue(ws.Cells[17 + v, 1].Value)))//判断是人民币还是外币
                    {
                        dis.cexch_name = sbv.cexch_name = "人民币";//币种
                        sbv.iExchRate = 1;//汇率
                        dis.iExchRate = 1;
                    }
                    else
                    {
                        try
                        {
                            if (GetCellStringValue(ws.Cells[17 + v, 1].Value).Contains("USD"))
                            {
                                dis.cexch_name = sbv.cexch_name = "美元";//币种
                                sbv.iExchRate = sb.exches.SingleOrDefault(p => p.itype == byte.Parse("2") && p.iperiod == byte.Parse(sbv.dDate.Month.ToString()) && p.cexch_name == "美元" && p.iYear == sbv.dDate.Year).nflat;//汇率
                                dis.iExchRate = sb.exches.SingleOrDefault(p => p.itype == byte.Parse("2") && p.iperiod == byte.Parse(sbv.dDate.Month.ToString()) && p.cexch_name == "美元" && p.iYear == sbv.dDate.Year).nflat;//汇率
                            }
                            else if (GetCellStringValue(ws.Cells[17 + v, 1].Value).Contains("JPY"))
                            {
                                dis.cexch_name = sbv.cexch_name = "日元";//币种
                                sbv.iExchRate = sb.exches.SingleOrDefault(p => p.itype == byte.Parse("2") && p.iperiod == byte.Parse(sbv.dDate.Month.ToString()) && p.cexch_name == "日元" && p.iYear == sbv.dDate.Year).nflat;//汇率
                                dis.iExchRate = sb.exches.SingleOrDefault(p => p.itype == byte.Parse("2") && p.iperiod == byte.Parse(sbv.dDate.Month.ToString()) && p.cexch_name == "日元" && p.iYear == sbv.dDate.Year).nflat;//汇率
                            }
                            else
                            {
                                dis.cexch_name = sbv.cexch_name = "人民币";//币种
                                sbv.iExchRate = 1;//汇率
                                dis.iExchRate = 1;
                            }
                        }
                        catch
                        {
                            str += string.Format("第 {0} 张发票外币信息不正确,请维护好后再导入!\n", count);
                            continue;
                        }
                    }
                    sb.SaleBillVouches.InsertOnSubmit(sbv);
                    sb.DispatchLists.InsertOnSubmit(dis);
                    sb.SubmitChanges();
                    #endregion
                    #region 生成子表
                    for (int i = 0; i < 8; i++)
                    {
                        sb = new SaleBillDataDataContext(str3);
                        if (GetCellStringValue(ws.Cells[5 + i + v, 5].Value) == "")
                        {
                            if (i == 0)
                            {
                                str += string.Format("第 {0} 张发票明细为空 ,请维护好后再导入!\n", count);
                            }
                            break;
                        }
                        count1++;
                        SaleBillVouch1 sbvs = new SaleBillVouch1();
                        DispatchList1 diss = new DispatchList1();
                        sbvs.SBVID = sbv.SBVID;//主表标识
                        sbvs.AutoID = maxautoid + count1;
                        sbvs.cbdlcode = sbv.cDLCode;
                        diss.cInvCode = sbvs.cInvCode = inventory.cInvCode;//存货编码
                        diss.iQuantity = sbvs.iQuantity = decimal.Parse(GetCellStringValue(ws.Cells[5 + v, 3].Value));//数量
                        diss.iTaxRate = sbvs.iTaxRate = decimal.Parse(sui == "" ? "0" : sui) * 100;//税率
                        diss.cInvName = sbvs.cInvName = inventory.cInvName;//存货名称

                        diss.iTaxUnitPrice = sbvs.iTaxUnitPrice = decimal.Parse(GetCellStringValue(ws.Cells[5 + v, 4].Value)) / decimal.Parse(sbv.iExchRate.ToString()).ToMathRound(2);//含税单价
                        diss.iSum = sbvs.iSum =Convert.ToDecimal(sbvs.iTaxUnitPrice * sbvs.iQuantity).ToMathRound(2);//含税金额
                        diss.iUnitPrice = sbvs.iUnitPrice = Convert.ToDecimal(sbvs.iTaxUnitPrice / (1 + (sbvs.iTaxRate / 100))).ToMathRound(2);//无税单价
                        diss.iMoney = sbvs.iMoney = Convert.ToDecimal(sbvs.iUnitPrice * sbvs.iQuantity).ToMathRound(2);//无税金额
                        diss.iTax = sbvs.iTax = sbvs.iSum - sbvs.iMoney;//税额

                        diss.iNatUnitPrice = sbvs.iNatUnitPrice = Convert.ToDecimal(sbvs.iUnitPrice * decimal.Parse(sbv.iExchRate.ToString())).ToMathRound(2);//本币无税单价
                        diss.iNatMoney = sbvs.iNatMoney = Convert.ToDecimal(sbvs.iMoney * decimal.Parse(sbv.iExchRate.ToString())).ToMathRound(2);//本币无税金额
                        diss.iNatTax = sbvs.iNatTax = Convert.ToDecimal(sbvs.iTax * decimal.Parse(sbv.iExchRate.ToString())).ToMathRound(2);//本币税额
                        diss.iNatSum = sbvs.iNatSum = Convert.ToDecimal(sbvs.iSum * decimal.Parse(sbv.iExchRate.ToString())).ToMathRound(2);//本币含税金额

                        diss.iQuotedPrice = sbvs.iQuotedPrice = 0;
                        sbvs.iMoneySum = sbvs.iExchSum = 0;
                        sbvs.iDisCount = 0; sbvs.iNatDisCount = 0; sbvs.iTB = 0; sbvs.fSalePrice = 0; sbvs.bgsp = false; sbvs.cMassUnit = 0; sbvs.bneedsign = false;
                        diss.iDLsID = iDLsID + count1;
                        sbvs.iDLsID = iDLsID + count1;
                        diss.DLID = dis.DLID;
                        diss.AutoID = maxautoid1 + count1;
                        diss.iCorID = 0;
                        diss.iDisCount = 0;
                        diss.iNatDisCount = 0;
                        diss.KL = diss.KL2 = sbvs.KL = sbvs.KL2 = 100;
                        #region 新增项目
                        if (!sb.fitemss00s.Any(p => p.citemcode == GetCellStringValue(ws.Cells[15 + v, 1].Value)))
                        {
                            fitemss00 fi = new fitemss00();
                            fi.citemcode = fi.citemname = GetCellStringValue(ws.Cells[15 + v, 1].Value);
                            fi.bclose = false;
                            fi.citemccode = "2";
                            fi.I_id = int.Parse(sb.fitemss00s.Max(p => p.I_id).ToString()) + 1;
                            sb.fitemss00s.InsertOnSubmit(fi);
                            sb.SubmitChanges();
                        }
                        #endregion
                        diss.cItemName = diss.cItemCode = sbvs.cItemName = sbvs.cItemCode = GetCellStringValue(ws.Cells[15 + v, 1].Value);
                        diss.cItem_CName = sbvs.cItem_CName = "D/N号";
                        diss.cItem_class = sbvs.cItem_class = "00";
                        sb.SaleBillVouch1s.InsertOnSubmit(sbvs);
                        sb.DispatchList1s.InsertOnSubmit(diss);
                        sb.SubmitChanges();
                    }
                    #endregion
                }
            }
            #endregion
            #region 普通发票导入
            else if (GetCellStringValue(ws.Cells[4, 0].Value) == "购货单位名称")
            {
                int NowCount = 0;
                for (int v = 4; v < ws.Cells.MaxDataRow; v += 24)
                {
                    NowCount++;
                    wf.SetWaitFormCaption(string.Format("正在导入第 {0} 张", NowCount));
                    #region 检查基础信息
                    count++;
                    Inventory inventory; Person person; Customer customer;
                    string temp = GetCellStringValue(ws.Cells[15 + v, 1].StringValue);
                    try
                    {
                        inventory = sb.Inventories.Single(p => p.cInvAddCode == GetInventoryCode(temp));
                    }
                    catch
                    {
                        str += string.Format("第 {0} 张发票D/N号(存货代码)在用友系统中不存在或有重复,请维护好后再导入!\n", count);
                        continue;
                    }
                    try
                    {
                        person = sb.Persons.Single(p => p.cPersonName == GetCellStringValue(ws.Cells[16 + v, 1].Value));
                    }
                    catch
                    {
                        str += string.Format("第 {0} 张发票业务员信息在用友系统不一致,请维护好后再导入!\n", count);
                        continue;
                    }
                    try
                    {
                        var _cCusName = GetCellStringValue(ws.Cells[0 + v, 1].Value);
                        customer = connNew.Query<Customer>($"select * from Customer where cCusName= '{_cCusName}'").FirstOrDefault();
                        
                    }
                    catch
                    {
                        str += string.Format("第 {0} 张发票客户信息在用友系统中不存在,请维护好后再导入!\n", count);
                        continue;
                    }
                    #endregion
                    #region 生成主表
                    SaleBillVouch sbv = new SaleBillVouch();
                    DispatchList dis = new DispatchList();
                    if (!string.IsNullOrEmpty(GetCellStringValue(ws.Cells[18 + v, 1].Value)))
                    {
                        dis.cSBVCode = sbv.cSBVCode = GetCellStringValue(ws.Cells[15 + v, 1].Value) + " #" + GetCellStringValue(ws.Cells[18 + v, 1].Value);//发票号
                    }
                    else
                    {
                        dis.cSBVCode = sbv.cSBVCode = GetCellStringValue(ws.Cells[15 + v, 1].Value);//发票号
                    }
                    if (sb.SaleBillVouches.Any(p => p.cSBVCode == sbv.cSBVCode))
                    {
                        str += string.Format("第 {0} 张发票在用友系统内已存在,导入失败!\n", count);
                        continue;
                    }
                    dis.SBVID = sbv.SBVID = maxid + count;//发票主表标识
                    dis.DLID = maxid1 + count;
                    dis.cDLCode = sbv.cDLCode = (int.Parse(maxid2) + count).ToString("0000000000");
                    sbv.cVouchType = "27"; //发票类型
                    sbv.bIAFirst = false;
                    dis.cVouchType = "05";
                    dis.cSTCode = sbv.cSTCode = "1";//销售类型编码
                    dis.dDate = sbv.dDate = DateTime.Today;//单据日期
                    dis.cDepCode = sbv.cDepCode = GetDepartCode(customer.cCCCode);//区域（501or601）
                    dis.cPersonCode = sbv.cPersonCode = person.cPersonCode;//人员编码
                    dis.cCusCode = sbv.cCusCode = customer.cCusCode;//客户编码
                    dis.cCusName = customer.cCusName;
                    dis.cPayCode = sbv.cPayCode = customer.cCusPayCond;//付款条件
                    string sui = GetCellStringValue(ws.Cells[5 + v, 6].Value).Replace('%', '\0');
                    dis.iTaxRate = sbv.iTaxRate = double.Parse(sui == "" ? "0" : sui) * 100;//税率
                    dis.cBusType = sbv.cBusType = "普通销售";//销售类型
                    sbv.cCusName = customer.cCusName;//客户名称
                    sbv.cSource = "销售";//单据来源
                    sbv.iVTid = 17;//单据模板号
                    dis.cMaker = dis.cVerifier = sbv.cChecker = sbv.cMaker = "蔡蕾";
                    dis.iSale = 1;
                    dis.iVTid = 71;
                    sbv.bIAFirst = dis.bIAFirst = false;
                    dis.csvouchtype = "27";
                    dis.bneedbill = true;
                    if (string.IsNullOrEmpty(GetCellStringValue(ws.Cells[17 + v, 1].Value)))//判断是人民币还是外币
                    {
                        dis.cexch_name = sbv.cexch_name = "人民币";//币种
                        sbv.iExchRate = 1;//汇率
                        dis.iExchRate = 1;
                    }
                    else
                    {
                        try
                        {
                            if (GetCellStringValue(ws.Cells[17 + v, 1].Value).Contains("USD"))
                            {
                                dis.cexch_name = sbv.cexch_name = "美元";//币种
                                sbv.iExchRate = sb.exches.SingleOrDefault(p => p.itype == byte.Parse("2") && p.iperiod == byte.Parse(sbv.dDate.Month.ToString()) && p.cexch_name == "美元" && p.iYear == sbv.dDate.Year).nflat;//汇率
                                dis.iExchRate = sb.exches.SingleOrDefault(p => p.itype == byte.Parse("2") && p.iperiod == byte.Parse(sbv.dDate.Month.ToString()) && p.cexch_name == "美元" && p.iYear == sbv.dDate.Year).nflat;//汇率
                            }
                            else if (GetCellStringValue(ws.Cells[17 + v, 1].Value).Contains("JPY"))
                            {
                                dis.cexch_name = sbv.cexch_name = "日元";//币种
                                sbv.iExchRate = sb.exches.SingleOrDefault(p => p.itype == byte.Parse("2") && p.iperiod == byte.Parse(sbv.dDate.Month.ToString()) && p.cexch_name == "日元" && p.iYear == sbv.dDate.Year).nflat;//汇率
                                dis.iExchRate = sb.exches.SingleOrDefault(p => p.itype == byte.Parse("2") && p.iperiod == byte.Parse(sbv.dDate.Month.ToString()) && p.cexch_name == "日元" && p.iYear == sbv.dDate.Year).nflat;//汇率
                            }
                            else
                            {
                                dis.cexch_name = sbv.cexch_name = "人民币";//币种
                                sbv.iExchRate = 1;//汇率
                                dis.iExchRate = 1;
                            }
                        }
                        catch
                        {
                            str += string.Format("第 {0} 张发票外币信息不正确,请维护好后再导入!\n", count);
                            continue;
                        }
                    }
                    sb.SaleBillVouches.InsertOnSubmit(sbv);
                    sb.DispatchLists.InsertOnSubmit(dis);
                    sb.SubmitChanges();
                    #endregion
                    #region 生成子表
                    for (int i = 0; i < 8; i++)
                    {
                        sb = new SaleBillDataDataContext(str3);
                        if (GetCellStringValue(ws.Cells[5 + i + v, 5].Value) == "")
                        {
                            if (i == 0)
                            {
                                str += string.Format("第 {0} 张发票明细为空 ,请维护好后再导入!\n", count);
                            }
                            break;
                        }
                        count1++;
                        SaleBillVouch1 sbvs = new SaleBillVouch1();
                        DispatchList1 diss = new DispatchList1();
                        sbvs.SBVID = sbv.SBVID;//主表标识
                        sbvs.AutoID = maxautoid + count1;
                        sbvs.cbdlcode = sbv.cDLCode;
                        diss.cInvCode = sbvs.cInvCode = inventory.cInvCode;//存货编码
                        diss.iQuantity = sbvs.iQuantity = decimal.Parse(GetCellStringValue(ws.Cells[5 + v, 3].Value));//数量
                        diss.iTaxRate = sbvs.iTaxRate = decimal.Parse(sui == "" ? "0" : sui);//税率

                        diss.iUnitPrice = sbvs.iUnitPrice = decimal.Parse(GetCellStringValue(ws.Cells[5 + v, 4].Value)) / decimal.Parse(sbv.iExchRate.ToString()).ToMathRound(2); //无税单价
                        diss.iMoney = sbvs.iMoney = Convert.ToDecimal(sbvs.iUnitPrice * sbvs.iQuantity).ToMathRound(2);//无税金额
                        diss.iTaxUnitPrice = sbvs.iTaxUnitPrice = Convert.ToDecimal(sbvs.iUnitPrice * (1 + sbvs.iTaxRate)).ToMathRound(2); //含税单价
                        diss.iSum = sbvs.iSum = Convert.ToDecimal(sbvs.iTaxUnitPrice * sbvs.iQuantity).ToMathRound(2);//含税金额
                        diss.iTax = sbvs.iTax = sbvs.iSum - sbvs.iMoney;//税额

                        diss.iNatUnitPrice = sbvs.iNatUnitPrice = Convert.ToDecimal(sbvs.iUnitPrice * decimal.Parse(sbv.iExchRate.ToString())).ToMathRound(2);//本币无税单价
                        diss.iNatMoney = sbvs.iNatMoney = Convert.ToDecimal(sbvs.iMoney * decimal.Parse(sbv.iExchRate.ToString())).ToMathRound(2);//本币无税金额
                        diss.iNatTax = sbvs.iNatTax = Convert.ToDecimal( sbvs.iTax * decimal.Parse(sbv.iExchRate.ToString())).ToMathRound(2);//本币税额
                        diss.iNatSum = sbvs.iNatSum = Convert.ToDecimal(sbvs.iSum * decimal.Parse(sbv.iExchRate.ToString())).ToMathRound(2);//本币含税金额

                        diss.cInvName = sbvs.cInvName = inventory.cInvName;//存货名称
                        sbvs.iMoneySum = sbvs.iExchSum = 0;
                        sbvs.iDisCount = 0; sbvs.iNatDisCount = 0; sbvs.iTB = 0; sbvs.fSalePrice = 0; sbvs.bgsp = false; sbvs.cMassUnit = 0; sbvs.bneedsign = false;
                        diss.iDLsID = iDLsID + count1;
                        sbvs.iDLsID = iDLsID + count1;
                        diss.DLID = dis.DLID;
                        diss.AutoID = maxautoid1 + count1;
                        diss.iCorID = 0;
                        diss.iDisCount = 0;
                        diss.iNatDisCount = 0;
                        diss.KL = diss.KL2 = sbvs.KL = sbvs.KL2 = 100;

                        #region 新增项目
                        if (!sb.fitemss00s.Any(p => p.citemcode == GetCellStringValue(ws.Cells[15 + v, 1].Value)))
                        {
                            fitemss00 fi = new fitemss00();
                            fi.citemcode = fi.citemname = GetCellStringValue(ws.Cells[15 + v, 1].Value);
                            fi.bclose = false;
                            fi.citemccode = "2";
                            fi.I_id = int.Parse(sb.fitemss00s.Max(p => p.I_id).ToString()) + 1;
                            sb.fitemss00s.InsertOnSubmit(fi);
                            sb.SubmitChanges();
                        }
                        #endregion
                        diss.cItemName = diss.cItemCode = sbvs.cItemName = sbvs.cItemCode = GetCellStringValue(ws.Cells[15 + v, 1].Value);
                        diss.cItem_CName = sbvs.cItem_CName = "D/N号";
                        diss.cItem_class = sbvs.cItem_class = "00";
                        sb.SaleBillVouch1s.InsertOnSubmit(sbvs);
                        sb.DispatchList1s.InsertOnSubmit(diss);
                        sb.SubmitChanges();
                    }
                    #endregion
                }
            }
            #endregion 
            #region 出错
            else
            {
                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                MessageBox.Show("选择的文件有误或者文件为空,请重新选择!", "提示");
                return;
            }
            #endregion 
            int ifather, ichild;
            ifather = int.Parse(SqlHelper.ExecuteScalar("select MAX(CAST(RIGHT(isnull(SBVID,0),8) AS INT)) from salebillvouch", str3, new SqlParameter()).ToString().Substring(1));
            ichild = int.Parse(SqlHelper.ExecuteScalar("select MAX(CAST(RIGHT(isnull(AutoID,0),8) AS INT)) from salebillvouchs", str3, new SqlParameter()).ToString().Substring(1));
            SqlHelper.ExecuteNonQuery(string.Format("update ufsystem.dbo.ua_identity set ifatherid = '{0}',ichildid = '{1}' where cacc_id = '{2}' and cvouchtype = 'BILLVOUCH'", ifather, ichild, ServerBox.Text.Substring(7, 3))
                , str3, new SqlParameter());
            ifather = int.Parse(SqlHelper.ExecuteScalar("select MAX(CAST(RIGHT(isnull(DLID,0),8) AS INT)) from DispatchList", str3, new SqlParameter()).ToString().Substring(1));
            ichild = int.Parse(SqlHelper.ExecuteScalar("select MAX(CAST(RIGHT(isnull(IdlsID,0),8) AS INT)) from DispatchLists", str3, new SqlParameter()).ToString().Substring(1));
            SqlHelper.ExecuteNonQuery(string.Format("update ufsystem.dbo.ua_identity set ifatherid = '{0}',ichildid = '{1}' where cacc_id = '{2}' and cvouchtype = 'Dispatch'", ifather, ichild, ServerBox.Text.Substring(7, 3))
                , str3, new SqlParameter());
            if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
            if (string.IsNullOrEmpty(str))
            {
                MessageBox.Show("数据导入成功!", "提示");
            }
            else
            {
                MessageBox.Show("数据导入成功,但是有没有导入的发票:\n" + str, "提示");
            }
        }
        //获取部门编号
        private string GetDepartCode(string o)
        {
            string s = "";
            switch (o)
            {
                case "01001":
                    s = "501";
                    break;
                case "01002":
                    s = "502";
                    break;
                case "02001":
                    s = "601";
                    break;
                case "02002":
                    s = "602";
                    break;
                case "02003":
                    s = "603";
                    break;
                case "02004":
                    s = "604";
                    break;
                default:
                    s = "502";
                    break;
            }
            return s;
        }
        //获取字符串
        private string GetCellStringValue(object o)
        {
            string s = (o == null ? "" : o.ToString());
            return s;
        }
        //获取存货编码
        public string GetInventoryCode(string temp)
        {
            temp = temp.Substring(temp.IndexOf("-") + 1);
            temp = temp.Substring(temp.IndexOf("-") + 1);
            foreach (var temp1 in temp)
            {
                if (char.IsNumber(temp1))
                {
                    temp = temp.Replace(temp1.ToString(), "");
                }
            }
            //temp += "01";
            return temp;
        }
        //客户导入
        private void simpleButton1_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "(*.xls,*.xlsx;)|*.xls;*.xlsx;";
            if (ofd.ShowDialog() == DialogResult.Cancel) return;
            var wf = new SplashScreenManager(this, typeof(DemoWaitForm), true, true);//进程
            wf.ShowWaitForm();
            wf.SetWaitFormCaption("正在导入...");
            int NowCount = 0, NewCount = 0;
            Workbook wb = new Workbook(ofd.FileName);
            Worksheet ws = wb.Worksheets[0];
            #region 校验
            if (ws.Cells[0, 0].StringValue != "客户公司名称" || ws.Cells[0, 1].StringValue != "公司税号" || ws.Cells[0, 2].StringValue != "公司地址电话" || ws.Cells[0, 3].StringValue != "公司开户行账号" || ws.Cells[0, 4].StringValue != "分类")
            {
                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                MessageBox.Show("选择的EXCEL文件格式错误,正确的格式为\n客户公司名称,公司税号,公司地址电话,公司开户行账号,分类", "提示");
                return;
            }
            #endregion
            var temp = sb.Customers.Where(p => p.cCusCode.ToString().Length == 5);
            var MaxCode = int.Parse(temp.Max(p => p.cCusCode).ToString()) + 1;
            for (int i = 1; i < ws.Cells.Rows.Count; i++)
            {
                NowCount++;
                wf.SetWaitFormCaption(string.Format("正在导入第 {0} 家客户", NowCount));
                string name = ws.Cells[i, 0].StringValue;
                if (sb.Customers.Any(p => p.cCusName == name))
                    continue;
                if (!sb.CustomerClasses.Any(p => p.cCCCode.ToString() == ws.Cells[i, 4].StringValue))
                    continue;
                Customer cu = new Customer();
                cu.cCusCreditCompany = cu.cCusHeadCode = cu.cCusCode = (MaxCode + NewCount).ToString();
                cu.cCusName = cu.cCusAbbName = name;
                cu.cCCCode = ws.Cells[i, 4].StringValue;
                cu.dCusCreateDatetime = DateTime.Today;
                cu.cCusExch_name = "人民币";
                cu.cCreatePerson = "蔡蕾";
                sb.Customers.InsertOnSubmit(cu);
                NewCount++;
            }
            sb.SubmitChanges();
            if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
            MessageBox.Show(string.Format("导入成功!共新增 {0} 家客户!", NewCount), "提示");
        }
        //成本导入
        private void OtherBtn_Click(object sender, EventArgs e)
        {
            #region 初始设置
            code = ServerBox.Text.Substring(7, 3);
            string str1 = con.Substring(0, con.IndexOf(" Catalog=") + 9);
            string str2 = con.Substring(con.IndexOf(" Catalog=") + 24);
            con = str1 + ServerBox.Text + str2;
            sb = new SaleBillDataDataContext(con);
            //获取新的连接字符串
            IDbConnection connNew = new SqlConnection(con);

            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "(*.xls,*.xlsx;)|*.xls;*.xlsx;";
            if (ofd.ShowDialog() == DialogResult.Cancel) return;
            var wf = new SplashScreenManager(this, typeof(DemoWaitForm), true, true);//进程
            wf.ShowWaitForm();
            wf.SetWaitFormCaption("正在导入...");
            string str = "";
            Workbook wb = new Workbook(ofd.FileName);
            Worksheet ws = wb.Worksheets[0];
            List<string> cPBVCodes = new List<string>();
            List<string> Memos = new List<string>();
            #endregion
            int count = 0;
            #region 表头字段
            string PBVID = "", cPBVBillType = "", cPBVCode = "", cPTCode = "", dPBVDate = "", cVenCode = "", cUnitCode = "",
                cDepCode = "", cPersonCode = "", cPayCode = "", cexch_name = "", cExchRate = "", iPBVTaxRate = "", cPBVMemo = "",
                 dSDate = "", cPBVMaker = "", iDiscountTaxType = "", cVerifier = "";
            #endregion
            #region 表体字段
            string ID = "", cInvCode = "", bExBill = "", dInDate = "", iPBVQuantity = "", iOriCost = "", iOriMoney = "", iOriTaxPrice = "", iOriSum = "", iCost = "", iMoney = "",
                iTaxPrice = "", iSum = "", iOriTotal = "", iTotal = "", cDebitHead = "", iTaxRate = "", cItemCode = "", cItem_class = "", cItemName = "", iOriTaxCost = "",
                bCosting = "", bTaxCost = "";
            #endregion
            #region 普通
            if (IsEffectC(ws) == "普通")
            {
                #region 创建DataTable
                DataTable ExcelTable = new DataTable();
                for (int i = 0; i < 15; i++)
                {
                    ExcelTable.Columns.Add(ws.Cells[0, i].StringValue.Trim());
                }
                for (int i = 1; i < ws.Cells.Rows.Count; i++)
                {
                    if (string.IsNullOrEmpty(ws.Cells[i, 0].StringValue.Trim()))
                        break;
                    DataRow dr = ExcelTable.NewRow();
                    ws.Cells[i, 15].PutValue("");
                    for (int j = 0; j < 15; j++)
                    {
                        #region 供应商
                        if (ws.Cells[0, j].StringValue == "供应商")
                        {
                            dr[j] = ws.Cells[i, j].StringValue.Trim();
                        }
                        #endregion
                        #region 部门
                        else if (ws.Cells[0, j].StringValue == "部门")
                        {
                            try
                            {
                                dr[j] = SqlHelper.ExecuteScalar(string.Format("select cDepCode from Department WHERE cDepName='{0}'", ws.Cells[i, j].StringValue.Trim())
                                    , con, new SqlParameter());
                            }
                            catch
                            {
                                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                                MessageBox.Show(string.Format("Excel第 {0} 行部门 {1} \n在用友系统中不存在或者不唯一!", i, ws.Cells[i, j].StringValue.Trim()), "提示");
                                return;
                            }
                        }
                        #endregion
                        #region 担当
                        else if (ws.Cells[0, j].StringValue == "担当")
                        {
                            try
                            {
                                dr[j] = SqlHelper.ExecuteScalar(string.Format("select cPersonCode from Person WHERE cPersonName='{0}'", ws.Cells[i, j].StringValue.Trim())
                                    , con, new SqlParameter());
                            }
                            catch
                            {
                                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                                MessageBox.Show(string.Format("Excel第 {0} 行担当 {1} \n在用友系统中不存在或者不唯一!", i, ws.Cells[i, j].StringValue.Trim()), "提示");
                                return;
                            }
                        }
                        #endregion
                        #region 备注
                        else if (ws.Cells[0, j].StringValue == "备注")
                        {
                            dr[j] = ws.Cells[i, j].StringValue.Trim();
                        }
                        #endregion
                        else
                        {
                            dr[j] = ws.Cells[i, j].StringValue.Trim();
                        }
                    }
                    ExcelTable.Rows.Add(dr);
                }
                try
                {
                    wb.Save(ofd.FileName);
                }
                catch
                {
                    if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                    MessageBox.Show("请关闭需要导入的Excel文件!", "提示");
                    return;
                }
                #endregion
                for (int i = 0; i < ExcelTable.Rows.Count; i++)
                {
                    count++;
                    if (!cPBVCodes.Contains(ExcelTable.Rows[i]["票号"].ToString()))
                    {
                        #region 判断用友中是否存在此发票
                        if (SqlHelper.ExecuteDataTable(string.Format("select * from purbillvouch where cPBVCode='{0}'", ExcelTable.Rows[i]["票号"].ToString())
                                     , con, new SqlParameter()).Rows.Count != 0)
                        {
                            for (int k = 1; k < ws.Cells.Rows.Count; k++)
                            {
                                if (ws.Cells[k, 0].StringValue.Trim() == ExcelTable.Rows[i]["票号"].ToString())
                                {
                                    ws.Cells[k, 15].PutValue("发票已导入");
                                }
                            }
                            wb.Save(ofd.FileName);
                            cPBVCodes.Add(ExcelTable.Rows[i]["票号"].ToString());
                            continue;
                        }
                        #endregion
                        DataRow[] drs = ExcelTable.Select(string.Format("票号 ='{0}'", ExcelTable.Rows[i]["票号"]));
                        #region 生成表头
                        #region 获取数值
                        int F_old = 0;
                        try
                        {
                            F_old = int.Parse(SqlHelper.ExecuteScalar(string.Format($"select iFatherId from UFSystem.dbo.UA_Identity where cAcc_Id='{code}' and cVouchType='PURBILL'")
                                 , con, new SqlParameter()).ToString());
                        }
                        catch
                        {
                            if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                            MessageBox.Show(string.Format("获取主表最大单据号失败!"), "提示");
                            return;
                        }
                        try
                        {
                            var _cVenCode = GetCellStringValue(ExcelTable.Rows[i]["供应商"].ToString());
                            cPayCode = connNew.Query<string>($"select cVenPayCond from Vendor where cVenCode= '{_cVenCode}'").FirstOrDefault();
                        }
                        catch
                        {
                            str += string.Format("第 {0} 张发票客户信息在用友系统中不存在,请维护好后再导入!\n", count);
                            continue;
                        }
                        PBVID = "1" + (F_old + 1).ToString("000000000");
                        cPBVBillType = "02";
                        cPBVCode = ExcelTable.Rows[i]["票号"].ToString();
                        cPTCode = "1";
                        dPBVDate = ExcelTable.Rows[i]["开票日期"].ToString();
                        cVenCode = ExcelTable.Rows[i]["供应商"].ToString();
                        cUnitCode = ExcelTable.Rows[i]["供应商"].ToString();
                        cDepCode = ExcelTable.Rows[i]["部门"].ToString();
                        cPersonCode = ExcelTable.Rows[i]["担当"].ToString();
                        //cPayCode = string.Empty;
                        cexch_name = ExcelTable.Rows[i]["币种"].ToString();
                        cExchRate = ExcelTable.Rows[i]["汇率"].ToString();
                        iPBVTaxRate = ExcelTable.Rows[i]["税率"].ToString();
                        cPBVMemo = string.Format("{0} #{1}", MergeMemo(drs), ExcelTable.Rows[i]["票号"]);
                        dSDate = ExcelTable.Rows[i]["开票日期"].ToString();
                        cPBVMaker = "成本发票";
                        iDiscountTaxType = "1";
                        cVerifier = "成本发票";
                        #endregion
                        string SqlMain = string.Format(@"insert into PurBillVouch(PBVID,cPBVBillType,cPBVCode,cPTCode,dPBVDate,cVenCode,cUnitCode,cDepCode,cPersonCode,cexch_name,cExchRate,iPBVTaxRate,cPBVMemo,cPBVMaker,iDiscountTaxType,bNegative,bOriginal,bFirst,iNetLock,iVTid,cSource,bCredit,iPrintCount,cVerifier,cPayCode)
                        values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',0,0,0,0,105,'应付',0,0,'{15}','{16}')"
                            , PBVID, cPBVBillType, cPBVCode, cPTCode, dPBVDate, cVenCode, cUnitCode, cDepCode, cPersonCode, cexch_name, cExchRate, iPBVTaxRate, cPBVMemo, cPBVMaker, iDiscountTaxType, cVerifier, cPayCode);
                        //执行语句
                        SqlHelper.ExecuteNonQuery(SqlMain, con, new SqlParameter());
                        SqlHelper.ExecuteNonQuery(string.Format($"UPDATE UFSystem.dbo.UA_Identity SET iFatherId={F_old + 1} WHERE cAcc_Id='{code}' and cVouchType='PURBILL'"), con, new SqlParameter());
                        #endregion
                        #region 生成表体
                        int C_old = 0;
                        for (int j = 0; j < drs.Count(); j++)
                        {
                            #region 获取数值
                            ID = cInvCode = bExBill = iPBVQuantity = iOriCost = iOriMoney = iOriTaxPrice = iOriSum = iCost = iMoney =
                            iTaxPrice = iSum = iOriTotal = iTotal = cDebitHead = iTaxRate = cItemCode = cItem_class = cItemName = iOriTaxCost =
                            bCosting = bTaxCost = "";
                            try
                            {
                                C_old = int.Parse(SqlHelper.ExecuteScalar(string.Format($"select iChildId from UFSystem.dbo.UA_Identity where cAcc_Id='{code}' and cVouchType='PURBILL'")
                                     , con, new SqlParameter()).ToString());
                            }
                            catch
                            {
                                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                                MessageBox.Show(string.Format("获取子表最大单据号失败!"), "提示");
                                return;
                            }
                            ID = "1" + (C_old + 1).ToString("000000000");
                            if (string.IsNullOrEmpty(GetInventory(drs[j][2].ToString())))
                            {
                                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                                MessageBox.Show(string.Format("{0} 对应的存货在用友系统中不存在!请添加后再导入", drs[j][2]));
                                SqlHelper.ExecuteNonQuery(string.Format("DELETE PurBillVouchs WHERE PBVID='{0}'", PBVID), con, new SqlParameter());
                                SqlHelper.ExecuteNonQuery(string.Format("DELETE PurBillVouch WHERE PBVID='{0}'", PBVID), con, new SqlParameter());
                                SqlHelper.ExecuteNonQuery(string.Format($"UPDATE UFSystem.dbo.UA_Identity SET iFatherId={F_old},iChildId={C_old - j} WHERE cAcc_Id='{code}' and cVouchType='PURBILL'"), con, new SqlParameter());
                                return;
                            }
                            //cInvCode = GetInventory(drs[j][2].ToString());

                            cInvCode = drs[j][11].ToString();
                            bExBill = "1";
                            iPBVQuantity = drs[j][5].ToString();
                            iOriCost = drs[j][6].ToString();
                            iOriMoney = drs[j][6].ToString();
                            iOriTaxPrice = (decimal.Parse(drs[j][9].ToString()) * 0.01M * decimal.Parse(drs[j][6].ToString())).ToString();
                            iOriSum = ((decimal.Parse(drs[j][9].ToString()) * 0.01M + 1) * decimal.Parse(drs[j][6].ToString())).ToString();
                            decimal _iMoney = decimal.Parse(drs[j][8].ToString()) * decimal.Parse(drs[j][6].ToString());
                            iCost = _iMoney.ToString("F2");
                            iMoney = _iMoney.ToString("F2");
                            iTaxPrice = (decimal.Parse(drs[j][9].ToString()) * 0.01M * _iMoney).ToString();
                            iSum = ((decimal.Parse(drs[j][9].ToString()) * 0.01M + 1) * _iMoney).ToString("F2");
                            iOriTotal = "0";
                            iTotal = "0";
                            cDebitHead = drs[j][7].ToString().Trim() == "人民币" ? "212101" : (drs[j][7].ToString().Trim() == "美元" ? "212102" : "212103");
                            iTaxRate = drs[j][9].ToString();
                            #region 新增项目
                            if (!sb.fitemss00s.Any(p => p.citemcode == drs[j][2].ToString()))
                            {
                                fitemss00 fi = new fitemss00();
                                fi.citemcode = fi.citemname = drs[j][2].ToString();
                                fi.bclose = false;
                                fi.citemccode = "2";
                                fi.I_id = int.Parse(sb.fitemss00s.Max(p => p.I_id).ToString()) + 1;
                                sb.fitemss00s.InsertOnSubmit(fi);
                                sb.SubmitChanges();
                            }
                            #endregion
                            cItemCode = drs[j][2].ToString();
                            cItem_class = "00";
                            cItemName = drs[j][2].ToString();
                            iOriTaxCost = ((decimal.Parse(drs[j][9].ToString()) * 0.01M + 1) * decimal.Parse(drs[j][6].ToString())).ToString();
                            bCosting = "0";
                            bTaxCost = "0";
                            #endregion
                            string SqlDetails = string.Format(@"INSERT INTO PurBillVouchs (ID , PBVID , cInvCode , bExBill , iPBVQuantity , iOriCost , iOriMoney , iOriTaxPrice , iOriSum , iCost , iMoney , iTaxPrice , iSum , iOriTotal , iTotal , cDebitHead , iTaxRate , cItemCode , cItem_class , cItemName , iOriTaxCost ,bCosting , bTaxCost)
                                                                                    values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}')"
                            , ID, PBVID, cInvCode, bExBill, iPBVQuantity, iOriCost, iOriMoney, iOriTaxPrice, iOriSum, iCost, iMoney,
                            iTaxPrice, iSum, iOriTotal, iTotal, cDebitHead, iTaxRate, cItemCode, cItem_class, cItemName, iOriTaxCost,
                            bCosting, bTaxCost);
                            try
                            {
                                SqlHelper.ExecuteNonQuery(SqlDetails, con, new SqlParameter());
                                SqlHelper.ExecuteNonQuery(string.Format($"UPDATE UFSystem.dbo.UA_Identity SET iChildId={C_old + 1} WHERE cAcc_Id='{code}' and cVouchType='PURBILL'"), con, new SqlParameter());
                            }
                            #region 出错回写
                            catch (Exception ex)
                            {
                                SqlHelper.ExecuteNonQuery(string.Format("DELETE PurBillVouchs WHERE PBVID='{0}'", PBVID), con, new SqlParameter());
                                SqlHelper.ExecuteNonQuery(string.Format("DELETE PurBillVouch WHERE PBVID='{0}'", PBVID), con, new SqlParameter());
                                SqlHelper.ExecuteNonQuery(string.Format($"UPDATE UFSystem.dbo.UA_Identity SET iFatherId={F_old},iChildId={C_old - j} WHERE cAcc_Id='{code}' and cVouchType='PURBILL'"), con, new SqlParameter());
                                for (int k = 1; k < ws.Cells.Rows.Count; k++)
                                {
                                    if (ws.Cells[k, 2].StringValue.Trim() == cItemCode)
                                    {
                                        ws.Cells[k, 15].PutValue(ex.Message);
                                        break;
                                    }
                                }
                                wb.Save(ofd.FileName);
                                break;
                            }
                            #endregion
                        }
                        cPBVCodes.Add(ExcelTable.Rows[i]["票号"].ToString());
                        #endregion
                    }
                }
            }
            #endregion
            #region 专用
            else if (IsEffectC(ws) == "专用")
            {
                #region 创建DataTable
                DataTable ExcelTable = new DataTable();
                for (int i = 0; i < 15; i++)
                {
                    ExcelTable.Columns.Add(ws.Cells[0, i].StringValue.Trim());
                }
                for (int i = 1; i < ws.Cells.Rows.Count; i++)
                {
                    if (string.IsNullOrEmpty(ws.Cells[i, 0].StringValue.Trim()))
                        break;
                    DataRow dr = ExcelTable.NewRow();
                    ws.Cells[i, 15].PutValue("");
                    for (int j = 0; j < 15; j++)
                    {
                        #region 供应商
                        if (ws.Cells[0, j].StringValue == "供应商")
                        {
                            dr[j] = ws.Cells[i, j].StringValue.Trim();
                            //try
                            //{
                            //    dr[j] = SqlHelper.ExecuteScalar(string.Format("select cVenCode from Vendor WHERE cVenName='{0}'", ws.Cells[i, j].StringValue.Trim())
                            //        , con, new SqlParameter());
                            //}
                            //catch
                            //{
                            //    if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                            //    MessageBox.Show(string.Format("Excel第 {0} 行供应商 {1} \n在用友系统中不存在或者不唯一!", i, ws.Cells[i, j].StringValue.Trim()), "提示");
                            //    return;
                            //}
                        }
                        #endregion
                        #region 部门
                        else if (ws.Cells[0, j].StringValue == "部门")
                        {
                            try
                            {
                                dr[j] = SqlHelper.ExecuteScalar(string.Format("select cDepCode from Department WHERE cDepName='{0}'", ws.Cells[i, j].StringValue.Trim())
                                    , con, new SqlParameter());
                            }
                            catch
                            {
                                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                                MessageBox.Show(string.Format("Excel第 {0} 行部门 {1} \n在用友系统中不存在或者不唯一!", i, ws.Cells[i, j].StringValue.Trim()), "提示");
                                return;
                            }
                        }
                        #endregion
                        #region 担当
                        else if (ws.Cells[0, j].StringValue == "担当")
                        {
                            try
                            {
                                dr[j] = SqlHelper.ExecuteScalar(string.Format("select cPersonCode from Person WHERE cPersonName='{0}'", ws.Cells[i, j].StringValue.Trim())
                                    , con, new SqlParameter());
                            }
                            catch
                            {
                                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                                MessageBox.Show(string.Format("Excel第 {0} 行担当 {1} \n在用友系统中不存在或者不唯一!", i, ws.Cells[i, j].StringValue.Trim()), "提示");
                                return;
                            }
                        }
                        #endregion
                        #region 备注
                        else if (ws.Cells[0, j].StringValue == "备注")
                        {
                            dr[j] = ws.Cells[i, j].StringValue.Trim();
                        }
                        #endregion
                        else
                        {
                            dr[j] = ws.Cells[i, j].StringValue.Trim();
                        }
                    }
                    ExcelTable.Rows.Add(dr);
                }
                try
                {
                    wb.Save(ofd.FileName);
                }
                catch
                {
                    if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                    MessageBox.Show("请关闭需要导入的Excel文件!", "提示");
                    return;
                }
                #endregion
                for (int i = 0; i < ExcelTable.Rows.Count; i++)
                {
                    if (!cPBVCodes.Contains(ExcelTable.Rows[i]["票号"].ToString()))
                    {
                        #region 判断用友中是否存在此发票
                        if (SqlHelper.ExecuteDataTable(string.Format("select * from purbillvouch where cPBVCode='{0}'", ExcelTable.Rows[i]["票号"].ToString())
                                     , con, new SqlParameter()).Rows.Count != 0)
                        {
                            for (int k = 1; k < ws.Cells.Rows.Count; k++)
                            {
                                if (ws.Cells[k, 0].StringValue.Trim() == ExcelTable.Rows[i]["票号"].ToString())
                                {
                                    ws.Cells[k, 15].PutValue("发票已导入");
                                }
                            }
                            wb.Save(ofd.FileName);
                            cPBVCodes.Add(ExcelTable.Rows[i]["票号"].ToString());
                            continue;
                        }
                        #endregion
                        DataRow[] drs = ExcelTable.Select(string.Format("票号 ='{0}'", ExcelTable.Rows[i]["票号"]));
                        #region 生成表头
                        #region 获取数值
                        int F_old = 0;
                        try
                        {
                            F_old = int.Parse(SqlHelper.ExecuteScalar(string.Format($"select iFatherId from UFSystem.dbo.UA_Identity where cAcc_Id='{code}' and cVouchType='PURBILL'")
                                 , con, new SqlParameter()).ToString());
                        }
                        catch
                        {
                            if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                            MessageBox.Show(string.Format("获取主表最大单据号失败!"), "提示");
                            return;
                        }
                        try
                        {
                            var _cVenCode = GetCellStringValue(ExcelTable.Rows[i]["供应商"].ToString());
                            cPayCode = connNew.Query<string>($"select cVenPayCond from Vendor where cVenCode= '{_cVenCode}'").FirstOrDefault();

                        }
                        catch
                        {
                            str += string.Format("第 {0} 张发票客户信息在用友系统中不存在,请维护好后再导入!\n", count);
                            continue;
                        }
                        PBVID = "1" + (F_old + 1).ToString("000000000");
                        cPBVBillType = "01";
                        cPBVCode = ExcelTable.Rows[i]["票号"].ToString();
                        cPTCode = "1";
                        dPBVDate = ExcelTable.Rows[i]["开票日期"].ToString();
                        cVenCode = ExcelTable.Rows[i]["供应商"].ToString();
                        cUnitCode = ExcelTable.Rows[i]["供应商"].ToString();
                        cDepCode = ExcelTable.Rows[i]["部门"].ToString();
                        cPersonCode = ExcelTable.Rows[i]["担当"].ToString();
                        //cPayCode = string.Empty;
                        cexch_name = "人民币";
                        cExchRate = "1";
                        iPBVTaxRate = ExcelTable.Rows[i]["税率%"].ToString();
                        cPBVMemo = string.Format("{0} #{1}", MergeMemo(drs), ExcelTable.Rows[i]["票号"]);
                        dSDate = ExcelTable.Rows[i]["开票日期"].ToString();
                        cPBVMaker = "成本发票";
                        iDiscountTaxType = "0";
                        cVerifier = "成本发票";
                        #endregion
                        string SqlMain = string.Format(@"insert into PurBillVouch(PBVID,cPBVBillType,cPBVCode,cPTCode,dPBVDate,cVenCode,cUnitCode,cDepCode,cPersonCode,cexch_name,cExchRate,iPBVTaxRate,cPBVMemo,cPBVMaker,iDiscountTaxType,bNegative,bOriginal,bFirst,iNetLock,iVTid,cSource,bCredit,iPrintCount,cVerifier,cPayCode)
                        values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}',0,0,0,0,35,'应付',0,0,'{15}','{16}')"
                            , PBVID, cPBVBillType, cPBVCode, cPTCode, dPBVDate, cVenCode, cUnitCode, cDepCode, cPersonCode, cexch_name, cExchRate, iPBVTaxRate, cPBVMemo, cPBVMaker, iDiscountTaxType, cVerifier, cPayCode);
                        //执行语句
                        SqlHelper.ExecuteNonQuery(SqlMain, con, new SqlParameter());
                        SqlHelper.ExecuteNonQuery(string.Format($"UPDATE UFSystem.dbo.UA_Identity SET iFatherId={F_old + 1} WHERE cAcc_Id='{code}' and cVouchType='PURBILL'"), con, new SqlParameter());
                        #endregion
                        #region 生成表体
                        int C_old = 0;
                        for (int j = 0; j < drs.Count(); j++)
                        {
                            #region 获取数值
                            ID = cInvCode = bExBill = iPBVQuantity = iOriCost = iOriMoney = iOriTaxPrice = iOriSum = iCost = iMoney =
                            iTaxPrice = iSum = iOriTotal = iTotal = cDebitHead = iTaxRate = cItemCode = cItem_class = cItemName = iOriTaxCost =
                            bCosting = bTaxCost = "";
                            try
                            {
                                C_old = int.Parse(SqlHelper.ExecuteScalar(string.Format($"select iChildId from UFSystem.dbo.UA_Identity where cAcc_Id='{code}' and cVouchType='PURBILL'")
                                     , con, new SqlParameter()).ToString());
                            }
                            catch
                            {
                                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                                MessageBox.Show(string.Format("获取子表最大单据号失败!"), "提示");
                                return;
                            }
                            ID = "1" + (C_old + 1).ToString("000000000");
                            if (string.IsNullOrEmpty(GetInventory(drs[j][2].ToString())))
                            {
                                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                                MessageBox.Show(string.Format("{0} 对应的存货在用友系统中不存在!请添加后再导入", drs[j][2]));
                                SqlHelper.ExecuteNonQuery(string.Format("DELETE PurBillVouchs WHERE PBVID='{0}'", PBVID), con, new SqlParameter());
                                SqlHelper.ExecuteNonQuery(string.Format("DELETE PurBillVouch WHERE PBVID='{0}'", PBVID), con, new SqlParameter());
                                SqlHelper.ExecuteNonQuery(string.Format($"UPDATE UFSystem.dbo.UA_Identity SET iFatherId={F_old},iChildId={ C_old - j} WHERE cAcc_Id='{code}' and cVouchType='PURBILL'"), con, new SqlParameter());
                                return;
                            }
                            //cInvCode = GetInventory(drs[j][2].ToString());
                            cInvCode = drs[j][11].ToString();
                            bExBill = "1";
                            iPBVQuantity = drs[j][6].ToString();
                            iOriCost = (decimal.Parse(drs[j][8].ToString()) / decimal.Parse(drs[j][6].ToString())).ToString();
                            iOriMoney = drs[j][8].ToString();
                            iOriTaxPrice = drs[j][9].ToString();
                            iOriSum = drs[j][10].ToString();
                            iCost = iOriCost;
                            iMoney = iOriMoney;
                            iTaxPrice = iOriTaxPrice;
                            iSum = iOriSum;
                            iOriTotal = "0";
                            iTotal = "0";
                            cDebitHead = "212101";
                            iTaxRate = drs[j][7].ToString();
                            #region 新增项目
                            if (!sb.fitemss00s.Any(p => p.citemcode == drs[j][2].ToString()))
                            {
                                fitemss00 fi = new fitemss00();
                                fi.citemcode = fi.citemname = drs[j][2].ToString();
                                fi.bclose = false;
                                fi.citemccode = "2";
                                fi.I_id = int.Parse(sb.fitemss00s.Max(p => p.I_id).ToString()) + 1;
                                sb.fitemss00s.InsertOnSubmit(fi);
                                sb.SubmitChanges();
                            }
                            #endregion
                            cItemCode = drs[j][2].ToString();
                            cItem_class = "00";
                            cItemName = drs[j][2].ToString();
                            iOriTaxCost = (decimal.Parse(drs[j][10].ToString()) / decimal.Parse(drs[j][6].ToString())).ToString();
                            bCosting = "0";
                            bTaxCost = "0";
                            #endregion
                            string SqlDetails = string.Format(@"INSERT INTO PurBillVouchs (ID , PBVID , cInvCode , bExBill , iPBVQuantity , iOriCost , iOriMoney , iOriTaxPrice , iOriSum , iCost , iMoney , iTaxPrice , iSum , iOriTotal , iTotal , cDebitHead , iTaxRate , cItemCode , cItem_class , cItemName , iOriTaxCost ,bCosting , bTaxCost)
                                                                                    values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}','{15}','{16}','{17}','{18}','{19}','{20}','{21}','{22}')"
                            , ID, PBVID, cInvCode, bExBill, iPBVQuantity, iOriCost, iOriMoney, iOriTaxPrice, iOriSum, iCost, iMoney,
                            iTaxPrice, iSum, iOriTotal, iTotal, cDebitHead, iTaxRate, cItemCode, cItem_class, cItemName, iOriTaxCost,
                            bCosting, bTaxCost);
                            try
                            {
                                SqlHelper.ExecuteNonQuery(SqlDetails, con, new SqlParameter());
                                SqlHelper.ExecuteNonQuery(string.Format($"UPDATE UFSystem.dbo.UA_Identity SET iChildId={C_old + 1} WHERE cAcc_Id='{code}' and cVouchType='PURBILL'"), con, new SqlParameter());
                            }
                            #region 出错回写
                            catch (Exception ex)
                            {
                                SqlHelper.ExecuteNonQuery(string.Format("DELETE PurBillVouchs WHERE PBVID='{0}'", PBVID), con, new SqlParameter());
                                SqlHelper.ExecuteNonQuery(string.Format("DELETE PurBillVouch WHERE PBVID='{0}'", PBVID), con, new SqlParameter());
                                SqlHelper.ExecuteNonQuery(string.Format($"UPDATE UFSystem.dbo.UA_Identity SET iFatherId={F_old},iChildId={C_old - j} WHERE cAcc_Id='{code}' and cVouchType='PURBILL'"), con, new SqlParameter());
                                for (int k = 1; k < ws.Cells.Rows.Count; k++)
                                {
                                    if (ws.Cells[k, 2].StringValue.Trim() == cItemCode)
                                    {
                                        ws.Cells[k, 15].PutValue(ex.Message);
                                        break;
                                    }
                                }
                                wb.Save(ofd.FileName);
                                break;
                            }
                            #endregion
                        }
                        cPBVCodes.Add(ExcelTable.Rows[i]["票号"].ToString());
                        #endregion
                    }
                }
            }
            #endregion
            #region 模板出错
            else
            {
                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                MessageBox.Show("选择的文件有误或者文件为空,请重新选择!", "提示");
                return;
            }
            #endregion
            #region 导出结果
            if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
            if (string.IsNullOrEmpty(str))
            {
                MessageBox.Show("数据导入完毕!导入结果请打开Excel查看", "提示");
            }
            else
            {
                MessageBox.Show("数据导入成功,但是有没有导入的发票:\n" + str, "提示");
            }
            #endregion
        }
        //应收应付
        private void AP_AR_Import_Click(object sender, EventArgs e)
        {
            #region 初始设置
            string str1 = con.Substring(0, con.IndexOf(" Catalog=") + 9);
            string str2 = con.Substring(con.IndexOf(" Catalog=") + 24);
            con = str1 + ServerBox.Text + str2;
            sb = new SaleBillDataDataContext(con);
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "(*.xls,*.xlsx;)|*.xls;*.xlsx;";
            if (ofd.ShowDialog() == DialogResult.Cancel) return;
            var wf = new SplashScreenManager(this, typeof(DemoWaitForm), true, true);//进程
            wf.ShowWaitForm();
            wf.SetWaitFormCaption("正在导入...");
            string str = "";
            Workbook wb = new Workbook(ofd.FileName);
            Worksheet ws = wb.Worksheets[0];
            try
            {
                wb.Save(ofd.FileName);
            }
            catch
            {
                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                MessageBox.Show("请关闭需要导入的Excel文件!", "提示");
                return;
            }
            string ARCodeCNY = "113312", ARCodeUSD = "113314", ARCodeJPY = "113313", APCodeCNY = "218113", APCodeUSD = "218115", APCodeJPY = "218114";//999科目
            //string ARCodeCNY = "113313", ARCodeUSD = "113314", ARCodeJPY = "113315", APCodeCNY = "218113", APCodeUSD = "218114", APCodeJPY = "218115";//102科目
            int MaxAuto_ID;
            List<string> errList1 = new List<string>();//币种不存在
            #endregion
            if (string.IsNullOrEmpty(IsEffectRP(ws)))
            {
                #region 创建DataTable
                DataTable ExcelTable = new DataTable();
                for (int i = 0; i < 18; i++)
                {
                    ExcelTable.Columns.Add(ws.Cells[0, i].StringValue.Trim());
                }
                for (int i = 1; i < ws.Cells.Rows.Count; i++)
                {
                    if (string.IsNullOrEmpty(ws.Cells[i, 0].StringValue.Trim()) || ws.Cells[i, 0].StringValue.Trim() == "科目对应")
                        break;
                    DataRow dr = ExcelTable.NewRow();
                    ws.Cells[i, 15].PutValue("");
                    for (int j = 0; j < 18; j++)
                    {
                        dr[j] = ws.Cells[i, j].StringValue.Trim();
                    }
                    ExcelTable.Rows.Add(dr);
                }
                #endregion
                #region 表头字段
                string cLink = "", cVouchType = "", cVouchID = "", dVouchDate = "", cDwCode = "", cDeptCode = "", cPerson = "",
                    cCode = "", cexch_name = "", iExchRate = "", bd_c = "0", iAmount = "", iAmount_f = "", iRAmount = "",
                     iRAmount_f = "", cPayCode = "", cOperator = "沈康慧", bStartFlag = "0", cFlag = "", iAmount_s = "0", iRAmount_s = "0"
                     , VT_ID = "", iClosesID = "0", iCoClosesID = "0", dcreatesystime = "", Auto_ID = "", iPrintCount = "0";
                #endregion
                #region 表体字段
                string cItem_Class = "", cItemCode = "", cDigest = "", cCodes = "", cItemName = "", iAmt_s = "0";
                #endregion
                for (int i = 0; i < ExcelTable.Rows.Count; i++)
                {
                    if (string.IsNullOrEmpty(ExcelTable.Rows[i]["日期"].ToString().Trim()))
                    {
                        break;
                    }
                    #region 生成应收单
                    #region 表头
                    #region 赋值
                    try
                    {
                        cVouchID = (int.Parse(SqlHelper.ExecuteScalar(string.Format("select MAX(cVouchID) from ap_vouch where cVouchType='R0'")
                                             , con, new SqlParameter()).ToString()) + 1).ToString("0000000000");
                        cVouchType = "R0";
                        cLink = cVouchType + cVouchID;
                        dVouchDate = ExcelTable.Rows[i]["日期"].ToString().Trim();
                        cDwCode = ExcelTable.Rows[i]["应收客户编码"].ToString().Trim();
                        cPerson = SqlHelper.ExecuteScalar(string.Format("select cPersonCode from Person where cPersonName='{0}'", ExcelTable.Rows[i]["业务员"]), con, new SqlParameter()).ToString();
                        cDeptCode = SqlHelper.ExecuteScalar(string.Format("select cDepCode from Person where cPersonName='{0}'", ExcelTable.Rows[i]["业务员"]), con, new SqlParameter()).ToString();
                        cexch_name = ExcelTable.Rows[i]["应收币种"].ToString().Trim();
                        if (cexch_name == "人民币")
                        {
                            cCode = ARCodeCNY;
                        }
                        else if (cexch_name == "美元")
                        {
                            cCode = ARCodeUSD;
                        }
                        else if (cexch_name == "日元")
                        {
                            cCode = ARCodeJPY;
                        }
                        else
                        {
                            errList1.Add(ExcelTable.Rows[i]["项目"].ToString().Trim());
                            continue;
                        }
                        iExchRate = ExcelTable.Rows[i]["应收汇率"].ToString().Trim();
                        iAmount = iRAmount = ExcelTable.Rows[i]["应收金额"].ToString().Trim();
                        iAmount_f = iRAmount_f = ExcelTable.Rows[i]["应收原币"].ToString().Trim();
                        DateTime TempDate = DateTime.Parse(ExcelTable.Rows[i]["日期"].ToString()).AddMonths(1);
                        cPayCode = SqlHelper.ExecuteScalar(string.Format("select cPayCode from PayCondition where iPayCreDays={0}", DateTime.DaysInMonth(TempDate.Year, TempDate.Month)), con, new SqlParameter()).ToString();
                        bd_c = "1";
                        cFlag = "AR";
                        VT_ID = "8054";
                        cDigest = "代收款" + ExcelTable.Rows[i]["项目"].ToString().Trim();
                        dcreatesystime = DateTime.Now.ToString();
                        MaxAuto_ID = int.Parse(SqlHelper.ExecuteScalar(string.Format("select iFatherId from UFSystem.dbo.UA_Identity where cAcc_Id='{0}' and cVouchType='RP'", ServerBox.Text.Substring(7, 3)), con, new SqlParameter()).ToString()) + 1;
                        Auto_ID = "1" + MaxAuto_ID.ToString("000000000");
                    }
                    catch (Exception ex)
                    {
                        if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                        MessageBox.Show("导入应收单主表出错!原因:" + ex.Message, "提示");
                        return;
                    }
                    #endregion
                    #region SQL语句
                    string MainSQL = string.Format(@"insert into ap_vouch (cLink, cVouchType, cVouchID, dVouchDate, cDwCode, cDeptCode, cPerson,cCode, cexch_name, iExchRate, bd_c , iAmount, iAmount_f, iRAmount,iRAmount_f, cPayCode, cOperator , bStartFlag , cFlag, iAmount_s , iRAmount_s , VT_ID, iClosesID , iCoClosesID , dcreatesystime, Auto_ID, iPrintCount,cDigest)
                                                                    values('{0}','{1}'      ,'{2}'    ,'{3}'      ,'{4}'   ,'{5}'     ,'{6}'   ,'{7}','{8}'      ,{9}       ,'{10}',{11}    ,{12}      ,{13}     ,{14}      ,'{15}'   ,'{16}'     ,'{17}'      ,'{18}',{19}       ,{20}        ,{21}  ,'{22}'     ,'{23}'       ,'{24}'         ,'{25}'  ,'{26}'      ,'{27}' )"
                        , cLink, cVouchType, cVouchID, dVouchDate, cDwCode, cDeptCode, cPerson, cCode, cexch_name, iExchRate, bd_c, iAmount, iAmount_f, iRAmount, iRAmount_f, cPayCode, cOperator, bStartFlag, cFlag, iAmount_s, iRAmount_s, VT_ID, iClosesID, iCoClosesID, dcreatesystime, Auto_ID, iPrintCount, cDigest
                        );
                    #endregion
                    #region 执行语句
                    try
                    {
                        SqlHelper.ExecuteNonQuery(MainSQL, con, new SqlParameter());
                        SqlHelper.ExecuteNonQuery(string.Format("update UFSystem.dbo.UA_Identity set iFatherId={0} where cAcc_Id='{1}' and cVouchType='RP'", MaxAuto_ID, ServerBox.Text.Substring(7, 3)), con, new SqlParameter());
                    }
                    catch (Exception ex)
                    {
                        if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                        MessageBox.Show(string.Format("生成应收单主表错误!原因:第{0}行,{1}", i + 2, ex.Message), "提示");
                        return;
                    }
                    #endregion
                    #endregion
                    #region 表体
                    #region 赋值
                    try
                    {
                        #region 新增项目
                        if (!sb.fitemss00s.Any(p => p.citemcode == ExcelTable.Rows[i]["项目"].ToString().Trim()))
                        {
                            fitemss00 fi = new fitemss00();
                            fi.citemcode = fi.citemname = ExcelTable.Rows[i]["项目"].ToString().Trim();
                            fi.bclose = false;
                            fi.citemccode = "2";
                            fi.I_id = int.Parse(sb.fitemss00s.Max(p => p.I_id).ToString()) + 1;
                            sb.fitemss00s.InsertOnSubmit(fi);
                            sb.SubmitChanges();
                        }
                        #endregion
                        cItem_Class = "00";
                        cItemCode = ExcelTable.Rows[i]["项目"].ToString().Trim();
                        cDigest = "代收款" + ExcelTable.Rows[i]["项目"].ToString().Trim();
                        cItemName = ExcelTable.Rows[i]["项目"].ToString().Trim();
                        cCodes = ExcelTable.Rows[i]["收款银行科目"].ToString().Trim();
                        iAmt_s = "1";
                        bd_c = "0";
                    }
                    catch (Exception ex)
                    {
                        if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                        SqlHelper.ExecuteNonQuery(string.Format("delete ap_vouch where cLink='{0}'", cLink), con, new SqlParameter());
                        SqlHelper.ExecuteNonQuery(string.Format("delete ap_vouchs where cLink='{0}'", cLink), con, new SqlParameter());
                        MessageBox.Show("导入应收单子表出错!原因:" + ex.Message, "提示");
                        return;
                    }
                    #endregion
                    #region SQL语句
                    string DetaliSQL = string.Format(@"insert into ap_vouchs (cLink, cDwCode, cDeptCode, cPerson,cItem_Class, cItemCode,cCode,cexch_name, iExchRate, bd_c , iAmount, iAmount_f, cItemName, iAmt_s,cDigest)
                                                                       values('{0}','{1}'   ,'{2}'     ,'{3}'   ,'{4}'      ,'{5}'     ,'{6}','{7}'     ,'{8}'     ,'{9}' ,{10}    ,{11}      ,'{12}'    ,'{13}' ,'{14}' )"
                        , cLink, cDwCode, cDeptCode, cPerson, cItem_Class, cItemCode, cCodes, cexch_name, iExchRate, bd_c, iAmount, iAmount_f, cItemName, iAmt_s, cDigest
                        );
                    #endregion
                    #region 执行语句
                    try
                    {
                        SqlHelper.ExecuteNonQuery(DetaliSQL, con, new SqlParameter());
                        int iChildId = int.Parse(SqlHelper.ExecuteScalar(string.Format("select iChildId from UFSystem.dbo.UA_Identity where cAcc_Id='{0}' and cVouchType='RP'", ServerBox.Text.Substring(7, 3)), con, new SqlParameter()).ToString()) + 1;
                        SqlHelper.ExecuteNonQuery(string.Format("update UFSystem.dbo.UA_Identity set iChildId={0} where cAcc_Id='{1}' and cVouchType='RP'", iChildId, ServerBox.Text.Substring(7, 3)), con, new SqlParameter());
                    }
                    catch (Exception ex)
                    {
                        #region 错误回写
                        if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                        SqlHelper.ExecuteNonQuery(string.Format("delete ap_vouch where cLink='{0}'", cLink), con, new SqlParameter());
                        SqlHelper.ExecuteNonQuery(string.Format("delete ap_vouchs where cLink='{0}'", cLink), con, new SqlParameter());
                        MessageBox.Show(string.Format("生成应收单子表错误!原因:第{0}行,{1}", i + 2, ex.Message), "提示");
                        return;
                        #endregion
                    }
                    #endregion
                    #endregion
                    #endregion
                    #region 生成应付单
                    #region 表头
                    #region 赋值
                    try
                    {
                        cVouchID = (int.Parse(SqlHelper.ExecuteScalar(string.Format("select MAX(cVouchID) from ap_vouch where cVouchType='P0'")
                                             , con, new SqlParameter()).ToString()) + 1).ToString("0000000000");
                        cVouchType = "P0";
                        cLink = cVouchType + cVouchID;
                        dVouchDate = ExcelTable.Rows[i]["日期"].ToString().Trim();
                        cDwCode = ExcelTable.Rows[i]["应付供应商编码"].ToString().Trim();
                        cPerson = SqlHelper.ExecuteScalar(string.Format("select cPersonCode from Person where cPersonName='{0}'", ExcelTable.Rows[i]["业务员"]), con, new SqlParameter()).ToString();
                        cDeptCode = SqlHelper.ExecuteScalar(string.Format("select cDepCode from Person where cPersonName='{0}'", ExcelTable.Rows[i]["业务员"]), con, new SqlParameter()).ToString();
                        cexch_name = ExcelTable.Rows[i]["应收币种"].ToString().Trim();
                        if (cexch_name == "人民币")
                        {
                            cCode = APCodeCNY;
                        }
                        else if (cexch_name == "美元")
                        {
                            cCode = APCodeUSD;
                        }
                        else if (cexch_name == "日元")
                        {
                            cCode = APCodeJPY;
                        }
                        else
                        {
                            errList1.Add(ExcelTable.Rows[i]["项目"].ToString().Trim());
                            continue;
                        }
                        iExchRate = ExcelTable.Rows[i]["应收汇率"].ToString().Trim();
                        iAmount = iRAmount = ExcelTable.Rows[i]["应收金额"].ToString().Trim();
                        iAmount_f = iRAmount_f = ExcelTable.Rows[i]["应收原币"].ToString().Trim();
                        DateTime TempDate = DateTime.Parse(ExcelTable.Rows[i]["日期"].ToString()).AddMonths(1);
                        cPayCode = SqlHelper.ExecuteScalar(string.Format("select cPayCode from PayCondition where iPayCreDays={0}", DateTime.DaysInMonth(TempDate.Year, TempDate.Month)), con, new SqlParameter()).ToString();
                        bd_c = "0";
                        cFlag = "AP";
                        VT_ID = "8056";
                        cDigest = "代付款" + ExcelTable.Rows[i]["项目"].ToString().Trim();
                        dcreatesystime = DateTime.Now.ToString();
                        MaxAuto_ID = int.Parse(SqlHelper.ExecuteScalar(string.Format("select iFatherId from UFSystem.dbo.UA_Identity where cAcc_Id='{0}' and cVouchType='RP'", ServerBox.Text.Substring(7, 3)), con, new SqlParameter()).ToString()) + 1;
                        Auto_ID = "1" + MaxAuto_ID.ToString("000000000");
                    }
                    catch (Exception ex)
                    {
                        if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                        MessageBox.Show(string.Format("导入应付单主表出错!原因:第{0}行,{1}", i + 2, ex.Message), "提示"); ;
                        return;
                    }
                    #endregion
                    #region SQL语句
                    MainSQL = string.Format(@"insert into ap_vouch (cLink, cVouchType, cVouchID, dVouchDate, cDwCode, cDeptCode, cPerson,cCode, cexch_name, iExchRate, bd_c , iAmount, iAmount_f, iRAmount,iRAmount_f, cPayCode, cOperator , bStartFlag , cFlag, iAmount_s , iRAmount_s , VT_ID, iClosesID , iCoClosesID , dcreatesystime, Auto_ID, iPrintCount,cDigest)
                                                                    values('{0}','{1}'      ,'{2}'    ,'{3}'      ,'{4}'   ,'{5}'     ,'{6}'   ,'{7}','{8}'      ,{9}       ,'{10}',{11}    ,{12}      ,{13}     ,{14}      ,'{15}'   ,'{16}'     ,'{17}'      ,'{18}',{19}       ,{20}        ,{21}  ,'{22}'     ,'{23}'       ,'{24}'         ,'{25}'  ,'{26}'      ,'{27}' )"
                        , cLink, cVouchType, cVouchID, dVouchDate, cDwCode, cDeptCode, cPerson, cCode, cexch_name, iExchRate, bd_c, iAmount, iAmount_f, iRAmount, iRAmount_f, cPayCode, cOperator, bStartFlag, cFlag, iAmount_s, iRAmount_s, VT_ID, iClosesID, iCoClosesID, dcreatesystime, Auto_ID, iPrintCount, cDigest
                        );
                    #endregion
                    #region 执行语句
                    try
                    {
                        SqlHelper.ExecuteNonQuery(MainSQL, con, new SqlParameter());
                        SqlHelper.ExecuteNonQuery(string.Format("update UFSystem.dbo.UA_Identity set iFatherId={0} where cAcc_Id='{1}' and cVouchType='RP'", MaxAuto_ID, ServerBox.Text.Substring(7, 3)), con, new SqlParameter());
                    }
                    catch (Exception ex)
                    {
                        if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                        MessageBox.Show("生成应付单主表错误!原因:" + ex.Message, "提示");
                        return;
                    }
                    #endregion
                    #endregion
                    #region 表体
                    #region 赋值
                    try
                    {
                        #region 新增项目
                        if (!sb.fitemss00s.Any(p => p.citemcode == ExcelTable.Rows[i]["项目"].ToString().Trim()))
                        {
                            fitemss00 fi = new fitemss00();
                            fi.citemcode = fi.citemname = ExcelTable.Rows[i]["项目"].ToString().Trim();
                            fi.bclose = false;
                            fi.citemccode = "2";
                            fi.I_id = int.Parse(sb.fitemss00s.Max(p => p.I_id).ToString()) + 1;
                            sb.fitemss00s.InsertOnSubmit(fi);
                            sb.SubmitChanges();
                        }
                        #endregion
                        cItem_Class = "00";
                        cItemCode = ExcelTable.Rows[i]["项目"].ToString().Trim();
                        cDigest = "代付款" + ExcelTable.Rows[i]["项目"].ToString().Trim();
                        cItemName = ExcelTable.Rows[i]["项目"].ToString().Trim();
                        cCodes = ExcelTable.Rows[i]["付款银行科目"].ToString().Trim();
                        iAmt_s = "1";
                        bd_c = "1";
                    }
                    catch (Exception ex)
                    {
                        if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                        SqlHelper.ExecuteNonQuery(string.Format("delete ap_vouch where cLink='{0}'", cLink), con, new SqlParameter());
                        SqlHelper.ExecuteNonQuery(string.Format("delete ap_vouchs where cLink='{0}'", cLink), con, new SqlParameter());
                        MessageBox.Show("导入应付单子表出错!原因:" + ex.Message, "提示");
                        return;
                    }
                    #endregion
                    #region SQL语句
                    DetaliSQL = string.Format(@"insert into ap_vouchs (cLink, cDwCode, cDeptCode, cPerson,cItem_Class, cItemCode,cCode,cexch_name, iExchRate, bd_c , iAmount, iAmount_f, cItemName, iAmt_s,cDigest)
                                                                       values('{0}','{1}'   ,'{2}'     ,'{3}'   ,'{4}'      ,'{5}'     ,'{6}','{7}'     ,'{8}'     ,'{9}' ,{10}    ,{11}      ,'{12}'    ,'{13}' ,'{14}' )"
                        , cLink, cDwCode, cDeptCode, cPerson, cItem_Class, cItemCode, cCodes, cexch_name, iExchRate, bd_c, iAmount, iAmount_f, cItemName, iAmt_s, cDigest
                        );
                    #endregion
                    #region 执行语句
                    try
                    {
                        SqlHelper.ExecuteNonQuery(DetaliSQL, con, new SqlParameter());
                        int iChildId = int.Parse(SqlHelper.ExecuteScalar(string.Format("select iChildId from UFSystem.dbo.UA_Identity where cAcc_Id='{0}' and cVouchType='RP'", ServerBox.Text.Substring(7, 3)), con, new SqlParameter()).ToString()) + 1;
                        SqlHelper.ExecuteNonQuery(string.Format("update UFSystem.dbo.UA_Identity set iChildId={0} where cAcc_Id='{1}' and cVouchType='RP'", iChildId, ServerBox.Text.Substring(7, 3)), con, new SqlParameter());
                    }
                    catch (Exception ex)
                    {
                        #region 错误回写
                        if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                        SqlHelper.ExecuteNonQuery(string.Format("delete ap_vouch where cLink='{0}'", cLink), con, new SqlParameter());
                        SqlHelper.ExecuteNonQuery(string.Format("delete ap_vouchs where cLink='{0}'", cLink), con, new SqlParameter());
                        MessageBox.Show(string.Format("生成应付单子表错误!原因:第{0}行,{1}", i + 2, ex.Message), "提示");
                        return;
                        #endregion
                    }
                    #endregion
                    #endregion
                    #endregion
                    #region 生成转账凭证
                    DateTime dbill_date = DateTime.Parse(ExcelTable.Rows[i]["日期"].ToString().Trim());
                    string iperiod = dbill_date.Month.ToString();
                    string csign = "转";
                    string isignseq = "3";
                    int ino_id = int.Parse(SqlHelper.ExecuteDataTable(string.Format("select max(ino_id) from gl_accvouch where csign='转' and iyear={0} and iperiod={1}", dbill_date.Year, dbill_date.Month), con, new SqlParameter()).Rows[0][0].ToString()) + 1;
                    int inid1 = 1;
                    int inid2 = 2;
                    string idoc = "-1";
                    string cbill = "沈康慧";
                    string ibook = "0";
                    string cdigest1 = ExcelTable.Rows[i]["项目"].ToString().Trim();
                    string ccode1 = "";
                    string ccode2 = "";
                    string cexch_name1 = cexch_name;
                    string md1 = iAmount;
                    string mc1 = "0";
                    string md_f1 = iAmount_f;
                    string mc_f1 = "0";
                    string md2 = "0";
                    string mc2 = iAmount;
                    string md_f2 = "0";
                    string mc_f2 = iAmount_f;
                    string nfrat = "0";
                    string nd_s = "0";
                    string nc_s = "0";
                    string iyear = dbill_date.Year.ToString();
                    string iYPeriod = dbill_date.Year.ToString("0000") + dbill_date.Month.ToString("00");
                    SqlHelper.ExecuteNonQuery(string.Format(@"insert into glaccvouch(iperiod,csign,isignseq,ino_id,inid,idoc,dbill_date,cbill,ibook,cdigest,ccode ,cexch_name,md  ,mc  ,md_f,mc_f,nfrat,nd_s,nc_s,iyear,iYPeriod)
                                                                              values({0}    ,'{1}',{2}     ,{3}   ,{4} ,{5} ,'{6}'     ,'{7}',{8}  ,'{9}'  ,'{10}','{11}'    ,{12},{13},{14},{15},{16} ,{17},{18},{19} ,'{20}'  )"
                        , iperiod, csign, isignseq, ino_id, inid1, idoc, dbill_date, cbill, ibook, cdigest1, ccode1, cexch_name1, md1, mc1, md_f1, mc_f1, nfrat, nd_s, nc_s, iyear, iYPeriod), con, new SqlParameter());
                    SqlHelper.ExecuteNonQuery(string.Format(@"insert into glaccvouch(iperiod,csign,isignseq,ino_id,inid,idoc,dbill_date,cbill,ibook,cdigest,ccode ,cexch_name,md  ,mc  ,md_f,mc_f,nfrat,nd_s,nc_s,iyear,iYPeriod)
                                                                              values({0}    ,'{1}',{2}     ,{3}   ,{4} ,{5} ,'{6}'     ,'{7}',{8}  ,'{9}'  ,'{10}','{11}'    ,{12},{13},{14},{15},{16} ,{17},{18},{19} ,'{20}'  )"
                        , iperiod, csign, isignseq, ino_id, inid1, idoc, dbill_date, cbill, ibook, cdigest1, ccode2, cexch_name1, md2, mc2, md_f2, mc_f2, nfrat, nd_s, nc_s, iyear, iYPeriod), con, new SqlParameter());
                    #endregion
                }
            }
            #region 模板出错
            else
            {
                if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                MessageBox.Show("选择的文件有误或者文件为空,请重新选择!", "提示");
                return;
            }
            #endregion
            #region 导出结果
            if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
            if (string.IsNullOrEmpty(str))
            {
                MessageBox.Show("数据导入完毕!", "提示");
            }
            else
            {
                MessageBox.Show("数据导入完毕!", "提示");
            }
            #endregion
        }
        //判断成本EXCEL有效性
        public string IsEffectC(Worksheet ws)
        {
            if (ws.Cells[0, 0].StringValue.Trim() == "票号" && ws.Cells[0, 2].StringValue.Trim() == "项目号"
                && ws.Cells[0, 3].StringValue.Trim() == "开票日期" && ws.Cells[0, 4].StringValue.Trim() == "担当" && ws.Cells[0, 5].StringValue.Trim() == "数量"
                && ws.Cells[0, 6].StringValue.Trim() == "原币" && ws.Cells[0, 7].StringValue.Trim() == "币种" && ws.Cells[0, 8].StringValue.Trim() == "汇率"
                && ws.Cells[0, 9].StringValue.Trim() == "税率" && ws.Cells[0, 10].StringValue.Trim() == "金额" && ws.Cells[0, 11].StringValue.Trim() == "科目"
                && ws.Cells[0, 12].StringValue.Trim() == "部门" && ws.Cells[0, 13].StringValue.Trim() == "备注")
            {
                return "普通";
            }
            else if (ws.Cells[0, 0].StringValue.Trim() == "票号" && ws.Cells[0, 2].StringValue.Trim() == "项目号"
                && ws.Cells[0, 3].StringValue.Trim() == "开票日期" && ws.Cells[0, 4].StringValue.Trim() == "担当" && ws.Cells[0, 5].StringValue.Trim() == "部门"
                && ws.Cells[0, 6].StringValue.Trim() == "数量" && ws.Cells[0, 7].StringValue.Trim() == "税率%" && ws.Cells[0, 8].StringValue.Trim() == "金额"
                && ws.Cells[0, 9].StringValue.Trim() == "税额" && ws.Cells[0, 10].StringValue.Trim() == "价税合计" && ws.Cells[0, 11].StringValue.Trim() == "科目"
                && ws.Cells[0, 12].StringValue.Trim() == "是否进项税额转出" && ws.Cells[0, 13].StringValue.Trim() == "备注")
            {
                return "专用";
            }
            else
            {
                return "";
            }
        }
        //判断应收应付EXCEL有效性
        public string IsEffectRP(Worksheet ws)
        {
            if (ws.Cells[0, 0].StringValue.Trim() == "日期" && ws.Cells[0, 1].StringValue.Trim() == "币种" && ws.Cells[0, 2].StringValue.Trim() == "项目"
                && ws.Cells[0, 3].StringValue.Trim() == "业务员" && ws.Cells[0, 4].StringValue.Trim() == "应收币种" && ws.Cells[0, 5].StringValue.Trim() == "应收原币"
                && ws.Cells[0, 6].StringValue.Trim() == "应收汇率" && ws.Cells[0, 7].StringValue.Trim() == "应收金额" && ws.Cells[0, 8].StringValue.Trim() == "应收客户简称"
                && ws.Cells[0, 9].StringValue.Trim() == "应收客户编码" && ws.Cells[0, 10].StringValue.Trim() == "收款银行科目" && ws.Cells[0, 11].StringValue.Trim() == "应付币种"
                && ws.Cells[0, 12].StringValue.Trim() == "应付原币" && ws.Cells[0, 13].StringValue.Trim() == "应付汇率" && ws.Cells[0, 14].StringValue.Trim() == "应付金额"
                && ws.Cells[0, 15].StringValue.Trim() == "应付供应商简称" && ws.Cells[0, 16].StringValue.Trim() == "应付供应商编码" && ws.Cells[0, 17].StringValue.Trim() == "付款银行科目")
            {
                return "";
            }
            else
            {
                return "出错";
            }
        }
        //合并备注
        public string MergeMemo(DataRow[] drs)
        {
            if (drs.Count() > 1)
            {
                string tempStr = drs[0][2].ToString().Substring(9);
                int i = 0;
                for (; i < tempStr.Length; i++)
                {
                    if (char.IsNumber(tempStr[i]))
                    {
                        break;
                    }
                }
                string str1 = drs[0][2].ToString().Substring(0, 9 + i);
                string str2 = "";
                for (int j = 0; j < drs.Count(); j++)
                {
                    if ((str1 + str2).Length + 10 > 50)
                    {
                        str2 = str2.Substring(0, str2.Length - 1) + "...";
                        return (str1 + str2);
                    }
                    else
                    {
                        str2 += (drs[j][2].ToString().Substring(drs[j][2].ToString().Length - 4) + "/");
                    }
                }
                str2 = str2.Substring(0, str2.Length - 1);
                return (str1 + str2);
            }
            else
            {
                return drs[0][2].ToString();
            }
        }
        //获取存货编码 NILS-17I-CSOE2106
        public string GetInventory(string Item_Num)
        {
            string str1 = con.Substring(0, con.IndexOf(" Catalog=") + 9);
            string str2 = con.Substring(con.IndexOf(" Catalog=") + 24);
            con = str1 + ServerBox.Text + str2;
            string tempStr = Item_Num.Substring(9);
            int i = 0;
            for (; i < tempStr.Length; i++)
            {
                if (char.IsNumber(tempStr[i]))
                {
                    break;
                }
            }
            try
            {
                return SqlHelper.ExecuteDataTable(string.Format("select cInvCode from Inventory where cInvAddCode ='{0}'", Item_Num.Substring(9, i)), con, new SqlParameter()).Rows[0][0].ToString();
            }
            catch
            {
                return "";
            }
        }
        //收入成本报表
        private void ReportBtn_Click(object sender, EventArgs e)
        {
            ReportForm rf = new ReportForm();
            rf.ServerText = ServerBox.Text;
            rf.mf = this;
            this.Hide();
            rf.ShowDialog();
            this.Show();
        }
        //预估报表
        private void ReportBtn2_Click(object sender, EventArgs e)
        {
            ReportForm2 rf = new ReportForm2();
            rf.ServerText = ServerBox.Text;
            rf.mf = this;
            this.Hide();
            rf.ShowDialog();
            this.Show();
        }
        //DN別
        private void simpleButton3_Click(object sender, EventArgs e)
        {
            ReportForm3 rf = new ReportForm3();
            rf.ServerText = ServerBox.Text;
            rf.mf = this;
            this.Hide();
            rf.ShowDialog();
            this.Show();
        }

        //各部门
        private void simpleButton2_Click(object sender, EventArgs e)
        {
            ReportForm4 rf = new ReportForm4();
            rf.ServerText = ServerBox.Text;
            rf.mf = this;
            this.Hide();
            rf.ShowDialog();
            this.Show();
        }

        private void simpleButton4_Click(object sender, EventArgs e)
        {
            #region 初始设置
            string str1 = con.Substring(0, con.IndexOf(" Catalog=") + 9);
            string str2 = con.Substring(con.IndexOf(" Catalog=") + 24);
            con = str1 + ServerBox.Text + str2;
            sb = new SaleBillDataDataContext(con);
            //获取新的连接字符串
            IDbConnection connNew = new SqlConnection(con);
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "(*.xls,*.xlsx;)|*.xls;*.xlsx;";
            if (ofd.ShowDialog() == DialogResult.Cancel) return;
            var wf = new SplashScreenManager(this, typeof(DemoWaitForm), true, true);//进程
            wf.ShowWaitForm();
            wf.SetWaitFormCaption("正在导入...");
            string str = "";
            Workbook wb = new Workbook(ofd.FileName);
            Worksheet ws = wb.Worksheets[0];
            List<string> cPBVCodes = new List<string>();
            List<string> Memos = new List<string>();
            #endregion

            #region 表头字段
            //客户简称,区域,
            string SBVID = "", iTaxRate = "", cCusName = "", cPersonCode = "", dDate = "", cSBVCode = "",
                cexch_name = "", cVouchType = "", cDepCode = "", cCusCode = "", iExchRate = "";
            #endregion
            #region 表体字段
            string cInvCode = "", iQuantity = "", iSum = "", cItemCode = "", AutoID = "", iNatSum = "";

            #endregion
            int sum = 0;
            #region 导入
            try
            {

                var maxid = Convert.ToInt32(SqlHelper.ExecuteDataTable(string.Format("select max(SBVID) from salebillvouch"), con, new SqlParameter()).Rows[0][0].ToString());
                var maxid2 = Convert.ToInt32(SqlHelper.ExecuteDataTable(string.Format("SELECT max(AutoID) from salebillvouchs"), con, new SqlParameter()).Rows[0][0].ToString());

                //excel 列表数据
                for (int i = 1; i < ws.Cells.Rows.Count; i++)
                {

                    if (!cPBVCodes.Contains(ws.Cells[i, 1].Value))
                    {
                        if (ws.Cells[i, 1].Value != null)
                        {
                            #region 判断用友中是否存在此发票
                            if (SqlHelper.ExecuteDataTable(string.Format("select * from SaleBillVouch where cSBVCode='{0}'", ws.Cells[i, 1].Value.ToString()), con, new SqlParameter()).Rows.Count == 0)
                            {

                                #region 新增项目
                                if (!sb.fitemss00s.Any(p => p.citemcode == ws.Cells[i, 1].Value.ToString()))
                                {
                                    fitemss00 fi = new fitemss00();
                                    fi.citemcode = fi.citemname = ws.Cells[i, 1].Value.ToString();
                                    fi.bclose = false;
                                    fi.citemccode = "2";
                                    fi.I_id = int.Parse(sb.fitemss00s.Max(p => p.I_id).ToString()) + 1;
                                    sb.fitemss00s.InsertOnSubmit(fi);
                                    sb.SubmitChanges();
                                }
                                #endregion

                                //业务员
                                var person = sb.Persons.Single(p => p.cPersonName == GetCellStringValue(ws.Cells[i, 4].Value.ToString()));
                                //币种
                                if (GetCellStringValue(ws.Cells[i, 3].Value.ToString()).Contains("USD"))
                                {
                                    cexch_name = "美元";//币种
                                    iExchRate = ws.Cells[i, 5].Value.ToString();//汇率
                                }
                                else if (GetCellStringValue(ws.Cells[i, 3].Value.ToString()).Contains("JPY"))
                                {
                                    cexch_name = "日元";//币种
                                    iExchRate = ws.Cells[i, 5].Value.ToString();//汇率
                                }
                                else
                                {
                                    cexch_name = "人民币";//币种
                                    iExchRate = "1";//汇率
                                }
                                maxid = maxid + 1;
                                SBVID = maxid.ToString(); iTaxRate = "0"; cCusName = ws.Cells[i, 0].Value.ToString(); cPersonCode = person.cPersonCode; cDepCode = ws.Cells[i, 9].Value.ToString();
                                dDate = DateTime.Now.ToString("yyyy-MM-dd"); cSBVCode = ws.Cells[i, 1].Value.ToString(); cVouchType = "27"; iNatSum = ws.Cells[i, 6].Value.ToString();
                                cCusCode = ws.Cells[i, 8].Value.ToString();
                                //var _cCusName = SqlHelper.ExecuteScalar("select cCusName from Customer where cCusCode = '" + cCusCode + "'", con, new SqlParameter()).ToString();
                                Customer customer = connNew.Query<Customer>($"select * from Customer where cCusCode= '{cCusCode}'").FirstOrDefault();
                                
                                string SqlMain = string.Format(@"INSERT dbo.SaleBillVouch (SBVID,iTaxRate,cPersonCode,cPayCode,dDate,cSBVCode,cexch_name,cVouchType,cDepCode,cCusCode,iExchRate,cCusName,iVTid,cSource,cstcode,cbustype,cMaker,bCredit,bcashsale,breturnflag,bfirst,cdefine7,idisp,cmodifier,dmoddate,ioutgolden,ccrechpname,dmodifysystime,cChecker ) 
                                                VALUES  ('{0}','{1}', '{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','17','销售','1','普通销售','销售开票','0','0','0','0','0','0','销售开票',getdate(),'0',null,getdate(),'销售开票')",
                                                        SBVID, iTaxRate, cPersonCode, customer.cCusPayCond, dDate, cSBVCode, cexch_name, cVouchType, cDepCode, cCusCode, iExchRate, customer.cCusName);
                                //执行语句
                                SqlHelper.ExecuteNonQuery(SqlMain, con, new SqlParameter());

                                maxid2 = maxid2 + 1;
                                AutoID = maxid2.ToString(); cInvCode = ws.Cells[i, 10].Value.ToString(); iQuantity = "1"; iSum = ws.Cells[i, 2].Value.ToString();
                                cItemCode = ws.Cells[i, 1].Value.ToString();
                                string SqlMainl = string.Format(@"INSERT dbo.SaleBillVouchs (SBVID,AutoID,cInvCode,iQuantity,iUnitPrice,iMoney,iSum,cItemCode,iTaxRate,iNatUnitPrice,iNatMoney,iNatSum,cItemName,cItem_class,cItem_CName ) 
                                                VALUES  ('{0}','{1}', '{2}','{3}','{4}','{4}','{4}','{5}','{6}','{7}','{7}','{7}','{5}','00','D/N号')",
                                                        SBVID, AutoID, cInvCode, iQuantity, iSum, cItemCode, iTaxRate, iNatSum);
                                //执行语句
                                SqlHelper.ExecuteNonQuery(SqlMainl, con, new SqlParameter());



                            }
                            #endregion
                        }
                    }
                    sum = i;
                }
                int ifather, ichild;
                ifather = int.Parse(SqlHelper.ExecuteScalar("select MAX(CAST(RIGHT(isnull(SBVID,0),8) AS INT)) from salebillvouch", con, new SqlParameter()).ToString().Substring(1));
                ichild = int.Parse(SqlHelper.ExecuteScalar("select MAX(CAST(RIGHT(isnull(AutoID,0),8) AS INT)) from salebillvouchs", con, new SqlParameter()).ToString().Substring(1));

                SqlHelper.ExecuteNonQuery(string.Format("update ufsystem.dbo.ua_identity set ifatherid = '{0}',ichildid = '{1}' where cacc_id = '{2}' and cvouchtype = 'BILLVOUCH'", ifather, ichild, ServerBox.Text.Substring(7, 3))
                     , con, new SqlParameter());
                wf.CloseWaitForm();
                MessageBox.Show("数据导入完毕!", "提示");
            }
            catch (Exception ex)
            {
                MessageBox.Show("第" + sum.ToString() + "条数据" + ex.Message, "提示");
                throw;
            }

            #endregion



        }
        /// <summary>
        /// 币种
        /// </summary>
        public List<CurrencyModel> currencys { get; set; }
        /// <summary>
        /// 凭证类型
        /// </summary>
        public List<VoucherTypeModel> voucherTypes { get; set; }
        /// <summary>
        /// 科目编码
        /// </summary>
        public List<CodeModel> codes { get; set; }
        /// <summary>
        /// 部门 辅助核算
        /// </summary>
        public List<DepartmentModel> departments { get; set; }
        /// <summary>
        /// 人员  辅助核算
        /// </summary>
        public List<PersonModel> persons { get; set; }
        /// <summary>
        /// 客户 辅助核算
        /// </summary>
        public List<CustomersModel> customers { get; set; }
        /// <summary>
        /// 供应商  辅助核算
        /// </summary>
        public List<VendorModel> vendors { get; set; }
        /// <summary>
        /// 项目 辅助核算
        /// </summary>
        public List<ProjectModel> probjects { get; set; }

        /// <summary>
        /// 凭证导入
        /// </summary>
        private void VoucherImport_Click(object sender, EventArgs e)
        {
            SplashScreenManager wf = null;
            try
            {
                #region 初始设置
                code = ServerBox.Text.Substring(7, 3);
                string str1 = con.Substring(0, con.IndexOf(" Catalog=") + 9);
                string str2 = con.Substring(con.IndexOf(" Catalog=") + 24);
                con = str1 + ServerBox.Text + str2;
                sb = new SaleBillDataDataContext(con);
                OpenFileDialog ofd = new OpenFileDialog();
                ofd.Filter = "(*.xls,*.xlsx;)|*.xls;*.xlsx;";
                if (ofd.ShowDialog() == DialogResult.Cancel) return;
                wf = new SplashScreenManager(this, typeof(DemoWaitForm), true, true);//进程
                wf.ShowWaitForm();
                wf.SetWaitFormCaption("正在导入...");
                Workbook wb = new Workbook(ofd.FileName);
                Worksheet ws = wb.Worksheets[0];
                List<string> cPBVCodes = new List<string>();
                List<string> Memos = new List<string>();
                #endregion

                #region 验证Excel文件格式
                //Excel文件列头
                List<string> cols = new List<string>
                {
                    "币种","日期","类型","记录编号","序号","制单人","科目类型","科目中文","项目",
                    "部门","个人","客商","业务员","借方金额","贷方金额","原币金额","明细标志","摘要"
                };
                if (!IsEffectVI(ws, cols)) throw new ArgumentException("选择的文件有误,请重新选择!");

                #endregion

                #region 创建DataTable
                DataTable ExcelTable = new DataTable();
                //添加DataTable列
                foreach (var col in cols)
                {
                    ExcelTable.Columns.Add(col);
                }
                //添加失败列,输出失败的数据
                string errCol = "失败";
                ExcelTable.Columns.Add(errCol);
                for (int i = 1; i < ws.Cells.Rows.Count; i++)
                {
                    DataRow dr = ExcelTable.NewRow();
                    for (int j = 0; j < cols.Count; j++)
                    {
                        dr[j] = ws.Cells[i, j].StringValue.Trim();
                    }
                    ExcelTable.Rows.Add(dr);
                }
                #endregion

                #region 组装数据结构
                if (ExcelTable.Rows.Count == 0) throw new ArgumentException("数据为空,无法导入!");

                #region 查询

                //币种
                currencys = DataHelper.QueryCurrency(con);
                //凭证类型
                voucherTypes = DataHelper.QueryVoucherType(con);
                //科目编码
                codes = DataHelper.QueryCode(con);
                //部门 辅助核算
                departments = DataHelper.QueryDepartment(con);
                //人员  辅助核算
                persons = DataHelper.QueryPerson(con);
                //客户 辅助核算
                customers = DataHelper.QueryCustomer(con);
                //供应商
                vendors = DataHelper.QueryVendor(con);
                //项目 辅助核算
                probjects = DataHelper.QueryProject(con);

                #endregion
                var dicCol = "记录编号";
                //根据序号去重
                DataTable dataTableDistinct = ExcelTable.DefaultView.ToTable(true, dicCol);

                //成功导入的数据凭证数量
                int VoucherImportCount = 0;
                foreach (DataRow rows in dataTableDistinct.Rows)
                {
                    //记录编号
                    var number = rows[dicCol]?.ToString();
                    //分凭证
                    DataRow[] dataRows = ExcelTable.Select($"{dicCol}='{number}'");
                    //序号不能为空
                    if (string.IsNullOrEmpty(number))
                    {
                        foreach (DataRow row in dataRows)
                        {
                            row[errCol] = $"[{dicCol}]不能为空";
                        }
                        //跳出当前循环
                        continue;
                    }
                    //使用Dapper建模
                    var gls = new List<GlAccvouchModel>();
                    //当前凭证是否存在错误
                    bool VoucherError = false;

                    #region 通用验证


                    //验证日期列是否为日期格式
                    var isdbill_date = DateTime.TryParse(dataRows[0]["日期"]?.ToString(), out DateTime dbill_date);
                    if (!isdbill_date)
                    {
                        VoucherError = true;
                        var errColTxt = dataRows[0][errCol]?.ToString();
                        string errTxt = "[日期]格式不正确";
                        dataRows[0][errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                    }
                    //验证凭证类型
                    string type = dataRows[0]["类型"]?.ToString();
                    int Isignseq = 0;
                    if (string.IsNullOrEmpty(type))
                    {
                        VoucherError = true;
                        var errColTxt = dataRows[0][errCol]?.ToString();
                        string errTxt = "[类型]不能为空";
                        dataRows[0][errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                    }
                    else if (!voucherTypes.Any(t => t.Csign == type))
                    {
                        VoucherError = true;
                        var errColTxt = dataRows[0][errCol]?.ToString();
                        string errTxt = "[类型]在用友系统中不存在";
                        dataRows[0][errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                    }
                    else Isignseq = voucherTypes.FirstOrDefault(t => t.Csign == type).Isignseq;
                    //制单人
                    string Cbill = dataRows[0]["制单人"]?.ToString();
                    if (string.IsNullOrEmpty(Cbill))
                    {
                        VoucherError = true;
                        var errColTxt = dataRows[0][errCol]?.ToString();
                        string errTxt = "[制单人]不能为空";
                        dataRows[0][errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                    }
                    #endregion

                    //头部存在保存是直接跳出
                    if (VoucherError) continue;

                    //当前最大凭证号
                    int voucherNo = DataHelper.QueryMaxVoucherNo(dbill_date.Year.ToString(), dbill_date.Month.ToString(), type, con);
                    voucherNo += 1;
                    int Inid = 1;
                    //循环填充数据
                    foreach (DataRow row in dataRows)
                    {
                        #region 分录验证

                        AdjustAccountsModel adjust = new AdjustAccountsModel();
                        //验证币种
                        string currency = row["币种"]?.ToString();
                        //当前分录币种是否为人民币
                        //为空时,默认人民币
                        bool ISRMB = string.IsNullOrEmpty(currency) || currency == "人民币" || currency.ToUpper() == "RMB" || currency.ToUpper() == "CNY";
                        bool CurrencyError = false;//币种是否存在错误
                        //币种为空时,默认人民币,不为空则需要验证
                        if (!ISRMB)
                        {
                            if (!currencys.Any(t => t.Cexch_name == currency))
                            {
                                if (!currencys.Any(t => t.Cexch_code == currency))
                                {
                                    CurrencyError = true;
                                    VoucherError = true;
                                    var errColTxt = row[errCol]?.ToString();
                                    string errTxt = "[币种]在用友系统中不存在";
                                    row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                                }
                                //存在给查询值
                                //币种名称
                                else currency = currencys.FirstOrDefault(t => t.Cexch_code == currency).Cexch_name;
                            }
                        }
                        //币种信息
                        CurrencyModel currencyModel = null;
                        //验证币种是否存在汇率
                        if (!CurrencyError && !ISRMB)
                        {
                            currencyModel = currencys.FirstOrDefault(t => t.Cexch_name == currency && t.iYear == dbill_date.Year && t.iperiod == dbill_date.Month);
                            if (currencyModel == null)
                            {
                                VoucherError = true;
                                var errColTxt = row[errCol]?.ToString();
                                string errTxt = $"[币种]的[{dbill_date:yyyyMM}汇率]在用友系统中不存在";
                                row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                            }
                        }
                        //科目编码
                        string code = row["科目类型"]?.ToString();
                        if (string.IsNullOrEmpty(code))
                        {
                            VoucherError = true;
                            var errColTxt = row[errCol]?.ToString();
                            string errTxt = "[科目类型]不能为空";
                            row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                        }
                        else if (!codes.Any(t => t.Ccode == code))
                        {
                            VoucherError = true;
                            var errColTxt = row[errCol]?.ToString();
                            string errTxt = "[科目类型]在用友系统中不存在";
                            row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                        }
                        else
                        {
                            var model = codes.FirstOrDefault(t => t.Ccode == code);
                            //是否为末级科目
                            if (!model.Bend)
                            {
                                VoucherError = true;
                                var errColTxt = row[errCol]?.ToString();
                                string errTxt = "[科目中文]不是末级科目";
                                row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                            }
                            //辅助核算验证
                            else adjust = SupplementaryAccounting(row, errCol, model, ref VoucherError);
                        }
                        #region 注释
                        //string code = row["科目中文"]?.ToString();
                        //if (string.IsNullOrEmpty(code))
                        //{
                        //    VoucherError = true;
                        //    var errColTxt = row[errCol]?.ToString();
                        //    string errTxt = "[科目中文]不能为空";
                        //    row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                        //}
                        //else
                        //{
                        //    //根据科目中文
                        //    bool codeError = false;
                        //    var codeModel = new CodeModel();
                        //    foreach (var _codeName in code.Split('/'))
                        //    {
                        //        var iQuery = codes.Where(t => 1 == 1);
                        //        if (!string.IsNullOrEmpty(codeModel.Ccode)) iQuery = iQuery.Where(t => t.Ccode.Contains(codeModel.Ccode));
                        //        var model = iQuery.FirstOrDefault(t => t.Ccode_name == _codeName);
                        //        if (model == null)
                        //        {
                        //            codeError = true;
                        //            VoucherError = true;
                        //            var errColTxt = row[errCol]?.ToString();
                        //            string errTxt = "[科目中文]在用友系统中不存在";
                        //            row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                        //            break;
                        //        }
                        //        //获取值
                        //        else codeModel = model;
                        //    }
                        //    //是否为末级科目
                        //    if (!codeModel.Bend)
                        //    {
                        //        VoucherError = true;
                        //        var errColTxt = row[errCol]?.ToString();
                        //        string errTxt = "[科目中文]不是末级科目";
                        //        row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                        //    }
                        //    if (!codeError)
                        //    {
                        //        //辅助核算验证
                        //        adjust = SupplementaryAccounting(row, errCol, codeModel, ref VoucherError);
                        //    }
                        //}
                        #endregion
                        //借方金额
                        string Md = row["借方金额"]?.ToString();
                        decimal _Md = 0;
                        if (!string.IsNullOrEmpty(Md))
                        {
                            if (!decimal.TryParse(Md, out _Md))
                            {
                                VoucherError = true;
                                var errColTxt = row[errCol]?.ToString();
                                string errTxt = "[借方金额]不是数字类型";
                                row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                            }
                        }
                        //贷方金额
                        string Mc = row["贷方金额"]?.ToString();
                        decimal _Mc = 0;
                        if (!string.IsNullOrEmpty(Mc))
                        {
                            if (!decimal.TryParse(Mc, out _Mc))
                            {
                                VoucherError = true;
                                var errColTxt = row[errCol]?.ToString();
                                string errTxt = "[贷方金额]不是数字类型";
                                row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                            }
                        }
                        //业务员
                        string personal = row["业务员"]?.ToString();
                        if (!string.IsNullOrEmpty(personal))
                        {
                            if (!persons.Any(t => t.cPsn_Name == personal))
                            {
                                if (!persons.Any(t => t.cPsn_Num == personal))
                                {
                                    VoucherError = true;
                                    var errColTxt = row[errCol]?.ToString();
                                    string errTxt = "[个人]在用友系统中不存在";
                                    row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                                }
                                //存在给查询值
                                else personal = persons.FirstOrDefault(t => t.cPsn_Num == personal).cPsn_Name;
                            }
                        }
                        //摘要
                        string _abstract = row["摘要"]?.ToString();
                        if (string.IsNullOrEmpty(_abstract))
                        {
                            VoucherError = true;
                            var errColTxt = row[errCol]?.ToString();
                            string errTxt = "[摘要]不能为空";
                            row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                        }
                        #endregion

                        //验证分录数据
                        if (VoucherError) continue;
                        //数据保留小数位
                        int DecimalPlaces = 2;
                        var accvouch = new GlAccvouchModel
                        {
                            //会计月
                            Iperiod = dbill_date.Month,
                            Csign = type,
                            Isignseq = Isignseq,
                            Ino_id = voucherNo,
                            Inid = Inid++,
                            Dbill_date = dbill_date,
                            Idoc = "-1",
                            Cbill = Cbill,
                            Ibook = "0",
                            Ccode = code,
                            //Cexch_name = currency,//币种
                            Cdigest = _abstract,
                            Md = Math.Round(_Md, DecimalPlaces),
                            Mc = Math.Round(_Mc, DecimalPlaces),
                            //Md_f = 0,
                            //Mc_f = 0,
                            //Nfrat = 0,
                            //Nd_s = 0,
                            //Nc_s = 0,
                            Iyear = dbill_date.Year,
                            IYPeriod = dbill_date.Year + dbill_date.Month.ToString("00"),
                            Ccus_id = adjust.cus,
                            Cdept_id = adjust.dept,
                            Citem_id = adjust.item,
                            Cperson_id = adjust.person,
                            Csup_id = adjust.sup,
                            Doutbilldate = dbill_date,
                            Cname = string.IsNullOrEmpty(personal) ? null : personal,
                            Citem_class = string.IsNullOrEmpty(adjust.item) ? null : probjects.FirstOrDefault(t => t.citemcode == adjust.item).citemccode
                        };
                        if (currencyModel != null)
                        {
                            //获取币种名称
                            accvouch.Cexch_name = currency;
                            accvouch.Nfrat = currencyModel.nflat;
                            //借方
                            if (accvouch.Md > 0) accvouch.Md_f = Math.Round(accvouch.Md / accvouch.Nfrat, DecimalPlaces);
                            //贷方                                                                        
                            if (accvouch.Mc > 0) accvouch.Mc_f = Math.Round(accvouch.Mc / accvouch.Nfrat, DecimalPlaces);
                        }
                        gls.Add(accvouch);
                    }
                    //验证是否存在数据
                    if (gls.Count == 0) continue;
                    //借方总金额
                    var SumMd = gls.Where(t => t.Mc == 0).Sum(s => s.Md);
                    //贷方总金额
                    var SumMc = gls.Where(t => t.Md == 0).Sum(s => s.Mc);
                    if (gls.Any(t => t.Mc != 0 && t.Md != 0))
                    {
                        foreach (DataRow row in dataRows)
                        {
                            var errColTxt = row[errCol]?.ToString();
                            string errTxt = "借贷金额同时不为0,无法导入!";
                            row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                        }
                    }
                    else if (SumMd != SumMc)
                    {
                        foreach (DataRow row in dataRows)
                        {
                            var errColTxt = row[errCol]?.ToString();
                            if (!string.IsNullOrEmpty(errColTxt))
                            {
                                string errTxt = "因出错导致(借贷金额不平),请先解决该记录之前的错误!";
                                row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                            }
                        }
                    }
                    else
                    {
                        //导入数据
                        var result = DataHelper.InsertVouchers(con, gls);
                        if (result == gls.Count)
                        {
                            VoucherImportCount++;
                            //删除已成功导入的数据
                            foreach (DataRow row in dataRows)
                            {
                                ExcelTable.Rows.Remove(row);
                            }
                        }
                    }
                }

                if (wf != null) if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                MessageBox.Show($"已成功导入{VoucherImportCount}张凭证", "提示");

                //如果DataTable行数大于0,说明存在错误的数据
                if (ExcelTable.Rows.Count > 0)
                {
                    var _path = Path.GetDirectoryName(ofd.FileName);
                    var _path2 = Path.GetFileNameWithoutExtension(ofd.FileName);
                    var _path3 = Path.GetExtension(ofd.FileName);
                    var SaveFileName = Path.Combine(_path, _path2 + "_失败数据" + DateTime.Now.ToString("ddHHmmss") + _path3);
                    ExcelHelper.ExportExcelWithAspose(ExcelTable, SaveFileName);
                    throw new ArgumentException("导入存在不正确的数据,文件已导出到源文件目录");
                }
                #endregion

            }
            catch (ArgumentException er)
            {
                if (wf != null) if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                MessageBox.Show(er.Message, "提示");
            }
            catch (Exception er)
            {
                if (wf != null) if (wf.IsSplashFormVisible) wf.CloseWaitForm();//关闭进程
                MessageBox.Show(er.Message, "提示");
            }

        }
        //判断凭证EXCEL有效性
        public bool IsEffectVI(Worksheet ws, List<string> cols)
        {

            for (int i = 0; i < 16; i++)
            {
                if (ws.Cells[0, i].StringValue.Trim() != cols[i])
                {
                    //return "出错";
                    return false;
                }
            }
            return true;
        }
        /// <summary>
        /// 辅助核算验证
        /// </summary>
        /// <param name="row">数据源</param>
        /// <param name="errCol">失败列名</param>
        /// <param name="code">科目编码</param>
        /// <param name="bperson">是否人员核算:1核算 0不核算</param>
        /// <param name="bcus">是否客户核算:1核算 0不核算</param>
        /// <param name="bsup">是否供应商核算:1核算 0不核算</param>
        /// <param name="bitem">是否项目核算:1核算 0不核算</param>
        /// <param name="bdept">是否部门核算:1核算 0不核算</param>
        /// <param name="VoucherError">当前是都存在错误</param>
        /// <returns></returns>
        public AdjustAccountsModel SupplementaryAccounting(DataRow row, string errCol, CodeModel model, ref bool VoucherError)
        {
            var adjust = new AdjustAccountsModel { Code = model.Ccode };

            //项目辅助核算
            if (model.Bitem == 1)
            {
                string poj = row["项目"]?.ToString();
                if (string.IsNullOrEmpty(poj))
                {
                    VoucherError = true;
                    var errColTxt = row[errCol]?.ToString();
                    string errTxt = $"科目编码[{model.Ccode}]的[项目]辅助核算不能为空";
                    row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                }
                else if (!probjects.Any(t => t.citemcode == poj))
                {
                    if (!probjects.Any(t => t.citemname == poj))
                    {
                        VoucherError = true;
                        var errColTxt = row[errCol]?.ToString();
                        string errTxt = "[项目]在用友系统中不存在";
                        row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                    }
                    //存在给查询值
                    else adjust.item = probjects.FirstOrDefault(t => t.citemname == poj).citemcode;
                }
                else adjust.item = poj;
            }

            //部门辅助核算
            if (model.Bdept == 1)
            {
                string dept = row["部门"]?.ToString();
                if (string.IsNullOrEmpty(dept))
                {
                    VoucherError = true;
                    var errColTxt = row[errCol]?.ToString();
                    string errTxt = $"科目编码[{model.Ccode}]的[部门]辅助核算不能为空";
                    row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                }
                else if (!departments.Any(t => t.cDepCode == dept))
                {
                    if (!departments.Any(t => t.cDepName == dept))
                    {
                        VoucherError = true;
                        var errColTxt = row[errCol]?.ToString();
                        string errTxt = $"[部门]在用友系统中不存在";
                        row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                    }
                    //存在给查询值
                    else adjust.dept = departments.FirstOrDefault(t => t.cDepName == dept).cDepCode;
                }
                else adjust.dept = dept;
            }

            //个人辅助核算
            if (model.Bperson == 1)
            {
                string personal = row["个人"]?.ToString();
                if (string.IsNullOrEmpty(personal))
                {
                    VoucherError = true;
                    var errColTxt = row[errCol]?.ToString();
                    string errTxt = $"科目编码[{model.Ccode}]的[个人]辅助核算不能为空";
                    row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                }
                else if (!persons.Any(t => t.cPsn_Num == personal))
                {
                    if (!persons.Any(t => t.cPsn_Name == personal))
                    {
                        VoucherError = true;
                        var errColTxt = row[errCol]?.ToString();
                        string errTxt = "[个人]在用友系统中不存在";
                        row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                    }
                    //存在给查询值
                    else
                    {
                        var person = persons.FirstOrDefault(t => t.cPsn_Name == personal);
                        adjust.person = person.cPsn_Num;
                        adjust.dept = person.cDept_num;
                    }
                }
                else
                {
                    var person = persons.FirstOrDefault(t => t.cPsn_Num == personal);
                    adjust.person = person.cPsn_Num;
                    adjust.dept = person.cDept_num;
                }
            }

            //客户/供应商辅助核算
            if (model.Bcus == 1 || model.Bsup == 1)
            {
                string cus_sup = row["客商"]?.ToString();
                if (string.IsNullOrEmpty(cus_sup))
                {
                    VoucherError = true;
                    var errColTxt = row[errCol]?.ToString();
                    string errTxt = $"科目编码[{model.Ccode}]的[客商]辅助核算不能为空";
                    row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                }
                else if (model.Bcus == 1)//客户 
                {
                    if (!customers.Any(t => t.cCusCode == cus_sup))
                    {
                        if (!customers.Any(t => t.cCusName == cus_sup))
                        {
                            VoucherError = true;
                            var errColTxt = row[errCol]?.ToString();
                            string errTxt = "[客户]在用友系统中不存在";
                            row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                        }
                        else adjust.cus = customers.FirstOrDefault(t => t.cCusName == cus_sup).cCusCode;
                    }
                    else adjust.cus = cus_sup;
                }
                else if (model.Bsup == 1) //供应商
                {
                    if (!vendors.Any(t => t.cVenCode == cus_sup))
                    {
                        if (!vendors.Any(t => t.cVenName == cus_sup))
                        {
                            VoucherError = true;
                            var errColTxt = row[errCol]?.ToString();
                            string errTxt = "[供应商]在用友系统中不存在";
                            row[errCol] = string.IsNullOrEmpty(errColTxt) ? errTxt : string.Join("|", new string[] { errColTxt, errTxt });
                        }
                        else adjust.sup = vendors.FirstOrDefault(t => t.cVenName == cus_sup).cVenCode;
                    }
                    else adjust.sup = cus_sup;
                }
            }
            return adjust;
        }
    }
}
